Logging or Log file creation

      Using SSIS Log file, we can see the package execution details (Success or failure). Using this package execution details we can find the failure point and reason for failure (if any failure is occurred during package execution).

Creating Log file:
      In the below example i am going to create loge file, the file format is text file.

• Open the required SSIS Package.
• Right click on the work pain and select "Logging" option.

• "Configure SSIS Logs:" window will open.
• Right side top corner of the window, we can find  a check box with the package name.Select that check box.

 

•  "Provider type:": From the "Provider type" drop down list select "SSIS log provider for text file" option.

• After that click on "Add" Button. This information will add into the container with a check box. Select the check box.

•  Click on "Configuration" then the drop down list will open. Select "New Connection" option.

•  "File Connection Manager Editor" window will open. Select "Usage type" as "Create file" from the drop down list. Use "Browse" button and give file path and name of the file.

•  Click on "OK" Button.

• After that we need to switch from "Providers and Log" tab to "Details" tab. So click on "Details" tab, highlighted on the above screen.

• In the above screen we can see the list of events with check box's. We need to select required events. Most frequently used events are "OnError, OnPreExecute and OnPostExecute".

•  Click on "OK" Button.
• Run the package. We can find the log file in the given path.

Foreach Loop Container

      Using Foreach loop container we can create repeating loops in control flow. Number of loops are depends on source information.

Sample package creation:
Example1: 
     I am having a folder, in that folder few source files are available, with same column names, same number of columns and data types. These files i need to load it into a table.

     This requirement we can achieve using "Foreach loop container" and "Data flow task". We need to specify source folder and file information in the container. We need to please "Data flow task" inside the container. Then the container verify the folder and it will create loops. Number of loops depends on source files in the specified folder. If folder having 4 files, container will loop 4 times. For each iteration it will pick one one file and it updates require variable with file path. Using this variable path data flow task will process one by one file.

Source Folder:

Source files data:

Target table creation Script:
                        CREATE TABLE SAMPLE_TBL
                        (
                        ID INT
                        ,NAME VARCHAR(50)
                        ,PRICE NUMERIC(12,4)
                        ,DESCRIPTION VARCHAR(100)
                        )

Package Creation:

• Open "BIDS"
• From "Control flow items" drag and drop the "Foreach Loop Container".

 

• Edit the "Foreach loop container" and go to "Collection". From the Enumerator drop down list select "For Each File Enumerator". Because we need to process files.

• Folder: Browse the source folder using "Browse" button or copy paste the source folder path.
• File: If it is "*.*" then "Foreach loop container" will process all files. If we put *.txt then it will process only text files. According to requirement we need to mention.
• Retrieve file name: Here three options are available.
• Fully qualified: If we select this option then Foreach loop container will pick the one by one file information (File full path including extension example: "D:\Programs\Forum\Source\Source\SourceFile1.txt) and it will assign to required variable.
• Name and extension: If we select this option then container will pick one by one file name along with extension and it will assign to specified variable. Example: SourceFile1.txt
• Name only: Container will pick one by one file name only. example:  SourceFile1
• Traverse subfolders: If the required folder is having sub folders and those folders also having source files, then we can select this option.

• Variable Mapping: Go to variable mappings (Click on "Variable mappings")
• Here we need to specify a variable. This variable dynamically will update, with one by one file information.
• Click on variable drop down list and select "<New Variable..>" option.
• This it will open "Add Variable" window.

• Give the Variable name (SourceFilePath) and Value type (File path is a string variable so we need to select String).
• Click on "OK" Button.
• Click on "Foreach Loop Editor" "OK" butto.
• We can see the created variable in the "variable list".

• Go to control flow tool box, drag and drop the "Data flow task" and place inside the "Foreach Loop Container".

• Edit the "Data Flow Task". From "Data Flow Sources" drag and drop "Flat file source" and configure with any one of the source file.
• From "Data Flow Destinations" drag and drop "OLE DB Destination" and configure with "Flat File Source".

• Go to Control flow.
• Now the source connection (Flat file source connection) is static. We need to make the source connection dynamic by assigning the variable "SourceFilePath".
• Right click on Source connection manager and select "properties" option.

• Go to properties window.
• Click on "Expressions" and edit.

• "Property Expressions Editor" Window will open.
• In "Property" Drop down list select "ConnectionString" and edit the Expression.

• "Expression Builder" will open.
• Click on "Variables" (On + sign). Then it will list all the available variable (System defined and user define variables).
• From the variable list drag and drop the "SourcePath" Variable into expression box.

• Then click on "OK" Button.
• Then after click on  "Property Expressions Editor" window "OK" Button.
• If we run the package "Validation Errors" will come. Because "SourceFilePath" variable value will come on run time only. So we need to set "Delay validation" property to "True" then validation errors will not come.
• To set this property: Don't select any task or container in the package, right click on plain and select properties.

• From the properties, In the "Execution" Section we can find "DelayValidation" from the drop down list select "True".

• Then Run the SSIS package.

Result from Target table: