Why not use a Script Task to gather the file list?
The built‑in enumerator is memory‑efficient, runs entirely within the SSIS engine, and automatically respects transactional boundaries set on the container.
| Name | Scope | Data Type | Default | Usage |
|------|-------|-----------|---------|-------|
| SourceFolder | Project | String | C:\ETL\Incoming | Base folder for file enumeration. |
| FileMask | Project | String | *.csv | Wildcard filter for the Foreach Loop. |
| TargetSchema | Project | String | dbo | Destination schema for all tables. |
| LoadDate | Package | DateTime | GETDATE() | Timestamp for audit columns. |
| CurrentFile | Loop | String | — | Holds the fully‑qualified file name for each iteration. |
| RowsProcessed | Package | Int32 | 0 | Incremented via a Script Component; used for email summary. |
Tip: Keep the number of package‑level variables low. Prefer Project Parameters for values that change per environment and Package Parameters for values that are specific to a single package run (e.g.,
LoadDate). SSIS-951.mp4
While this article treats "SSIS-951.mp4" as an objective case study in digital file naming, it is crucial to highlight the security risks associated with searching for and downloading files with these types of names.
Because these files are inherently tied to copyright infringement, they are almost entirely distributed outside of official, moderated channels. As a result: Why not use a Script Task to gather the file list
The demo package, named Load_FinancialTransactions.dtsx, follows a four‑stage pipeline:
| Stage | Control‑Flow Component | Purpose |
|-------|------------------------|---------|
| 0 | Variables & Parameters | Global parameters (SourceFolder, FileMask, TargetSchema, LoadDate) plus a Project‑level parameter for the Azure Storage SAS token. |
| 1 | Foreach Loop Container | Enumerates all *.csv files under SourceFolder (e.g., C:\ETL\Incoming\*.csv) and sets the current file path to the variable CurrentFile. |
| 2 | Data Flow Task (DFT_Transform_Transactions) | Reads the CSV, applies a Script Component for custom cleansing, splits rows using a Conditional Split, and directs them to three destinations: a Staging Table, an Error Table, and a Dimensional SCD Type 2 table. |
| 3 | Execute SQL Task (SQL_Apply_SCD) | Runs a set‑based MERGE statement that implements the SCD Type 2 logic on the target dimension. |
| 4 | Send Mail Task (Notify_On_Failure) | Fires only on package failure (wired through an OnError event handler). | | Name | Scope | Data Type |
All components are parameterized so the same package can be promoted across environments (DEV → TEST → PROD) without code changes.