Content
Exercise 1: create a package that will generate 12 folders.
Exercise 2: Load three (or multiple) .txt files into SQL server.
Exercise 3: How to import tables from different servers.
There are different types of enumerators in the SSIS Foreach Loop Container. You can select the enumerator type from the collection tab within the SSIS Foreach Loop Container editor form:
Foreach item enumerator: Loop over a set of items that can be defined manually within the SSIS Foreach Loop Container editor
Foreach File enumerator: Loop over files within a specific directory
Foreach ADO enumerator: Loop over file rows in an ADO Recordset
Foreach ADO.NET Schema Rowset enumerator: Loop over schema information from a specific data source (tables in a database)
Foreach from Variable enumerator: Loop over items stored within an SSIS variable of type object (must be enumerable)
Foreach NodeList enumerator: Loop over a result set of an XML Path Language (XPath) expression
Foreach SMO enumerator: Loop over SQL Server Management Objects (SMO) objects, such as available servers
Foreach HDFS File enumerator: Loop over files located within a Hadoop distributed file system directory
Foreach Azure Blob: Loop over blobs in a blob container in Azure Storage
Foreach ADLS File: Loop over files in a directory in Azure Data Lake Store
Foreach Data Lake Storage Gen2 File: Loop over files in a directory in Azure Data Lake Store Gen2
Exercise 1:
I want to create a package that will generate 12 folders (CurrentYear_months)
step 1: I am going to use Foreach Item Emumerator.
(Foreach item enumerator: Loop over a set of items that can be defined manually within the SSIS Foreach Loop Container editor)
step 2:
2.1 click on the button Columns.
2.2 add new column: Column 0
2.3 data type: string
step 3:
Add 12 months
step 4: go to Variable Mapping and create a variable that will map these months.
step 5: I will use File System Task to generate these directories for me. I need to prepare couple of variables.
@MonthNames: I have created this variable for the container
@DestinationFolder: this variable holds the path to the place where the folders should be created.
@ConnectionManager: I will join @DestinationFolder + Year(getdate())+@MonthNames and use this variable for File system task to create the directiries.
step 6
Click on the dots in Expression to start the Expression Builder.
step 7: build the expression
@[User::DestinationFolder] + (DT_WSTR, 4)Year(getdate())+"_"+ @[User::MonthNames]
step 8: Bring the File system task inside the Foreach Loop Container. Set it up to create directory. Use @ConnectionManager as a SourceVariable
step 9 : run the package
Exercise 2:
Load three (or multiple) .txt files into SQL server.
step 1: Create a variable @FolderPath, value of the variable is the path to the directory with files for loading.
Step 2:
Get Foreach Loop Container in.
Enumerator: Foreach File Enum (Loops over files within a specific directory)
Expressions: click on the dots.
Property Expression Editor: Find Directory
Expression: click on the dots
[..or you can just click on the Browse button and set it up without expression.]
Step 3: add the variable @FolderPath in , close it and go to Variable mapping.
Step 4: add a NEW variable @FileName. This variable will hold .txt file names.
Click on OK to come out from the setting.
Step 5 : Bring the Data Flow Task.
Step 6: Set up the Flat File Source
-click on Browse, load 1 file, Foreach loop Container will loop throw all of them.
Go to Advanced to check the data types, Columns, Preview...
Step 7: Set up the destination
Now I am going to set up the Flat File Connection Manager to loop throw the .txt files and extract them all.
7.1 click on the Flat File Con. Manager,
7.2 Expressions
7.3 in the Property Expressions Editor: ConnectionString.
step 8: write the expression
@[User::FolderPath]+ @[User::FileName]
step 9 : run the package
Exercise 3:
How to import tables from different servers
I have 2 tables on 2 different servers and I want to load these data into the 3. server.
For the data loading, I will need the server name and the database name. I am going to get them from a server table.
Step 1: create 3 variables for server and database names.
(@vTable, data type: object) is not on this picture.
Step 2: add Execute SQL Task to fetch these values from SQL Server into @vTable variable
ResultSet: Full result set
SQLStatement: Select [ServerName], [DatabaseName] from Config_Servers where IsActive=1
Step 3: go to Result Set, map @vTable as a Result set variable. You can change the Result Name to 0.
Step 4: Add Foreach loop container.
To loop through the object variable use Foreach ADO Enumerator
ADO object source variable: @vTable.
Step 5: map the variables for @vTable.
Step 6: Add Data Flow Task
Step 7: configure source
Step 8: Configure destination
Step 9: I need to convert the source connection to expression by using Property Expression Editor.
9.1 click on the source connection, press F4 to open Properties
9.2 click on the dots [...] next to the Expressions in properties
9.3 Property ServerName: assign variable @ServerName
9.4 InitialCatalog: assign variable @DatabaseName
click OK
Step 10 : UNCHECK Work Offline
Step 11 : Run the package.
