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:

Run a SSIS Package Using Batch File

     We can create a batch file to run the SSIS package, using DTExec utility (32 bit or 64 bit utility). We can pass variables value also through batch file.

Note: Follow the below command and prepare a text file. Save the text file with ".bat" extension. If we double click on the batch file, then package execution will start.

Batch (.bat) file for SSIS package using 32 bit utility:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f  "D:\Sample_Packages\Test_Package.dtsx"

 Batch (.bat) file for SSIS package using 64 bit utility:
 "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f  "D:\Sample_Packages\Test_Package.dtsx"

 Passing variable value through batch file:
 "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"  /f  "D:\Sample_Packages\Test_Package.dtsx" /SET \Package.Variables[User::Variable_Name].Value;"Variable_Value"

Note: If we need to pass variable value as a path (file or folder), in that path we need to replace "\" with "\\".

Example: If required path is D:\Sample_Packages\Source_Folder\Sample.txt. We need to pass this path like this D:\\Sample_Packages\\Source_Folder\\Sample.txt in a batch file.



DTExec File paths:  DTExec file path is depends on SQL Server/SSIS version.

Note:	1. The below listed paths are available in 64 Bit computer. 2. On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or SQL Server Data Tools (SSDT) during setup.
SQL Server Version	DTExec File Path
	32 Bit		64 Bit
SQL Server 2005	<drive>:\Program Files(x86)\Microsoft SQL Server\90\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\90\DTS\Binn
SQL Server 2008	<drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn
SQL Server 2008 R2	<drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn
SQL Server 2012	<drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn
SQL Server 2014	<drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn
SQL Server 2016	<drive>:\Program Files(x86)\Microsoft SQL Server\110\DTS\Binn		<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn

Video: Execute SSIS Package from Batch file or Command prompt 

Diffrence betwee Lookup, Fuzzy Lookup and Fuzzy Grouping

Look-Up:
           Using Lookup Transformation we can find the exact matched records.

Fuzzy Lookup:
           Using Fuzzy Lookup Transformation, we can find the percentage matches between two strings.

Fuzzy Grouping:


           Using Fuzzy Grouping Transformation, we can find the duplicate records.

Diffrence Between Merge and Merge Join

Merge:

• Using Merge Transformation, We can combine two sorted data sets into single data set.
• The Merge Transformation similar to 'Union all'.
• The two data sets, Number of columns and data types should match.

Merge Join:

• Using Merge Join Transformation, We can Perform 'Join' Operation using two sorted data sets.
• We can perform 'Inner Join, Left Outer and Full Outer Join.
•  The two data sets, number of columns and data types can be differ but the Key columns (Using to join two data sets) data types should match.

MERGE JOIN TRANSFORMATION

            Using Merge Join transformation we can join two sorted data sets based on common column. We can perform “Inner join, Left outer join and Full outer join" using Merge join transformation.

Sample package creation:

Requirement:-

Two source tables are available EMP (is having Employee information) and DEPT (is having department information).

EMP Table:-

CREATE TABLE EMP (ID INT,NAME VARCHAR(20),DEPT_ID INT)

GO

INSERT INTO EMP VALUES (101,'A',10)

INSERT INTO EMP VALUES (102,'B',20)

INSERT INTO EMP VALUES (103,'C',10)

INSERT INTO EMP VALUES (104,'D',30)

DEPT Table:-

CREATE TABLE DEPT (DEPT_ID INT,LOC VARCHAR(20))

GO

INSERT INTO DEPT VALUES (10,'Location1')

INSERT INTO DEPT VALUES (20,'Location2')

INSERT INTO DEPT VALUES (30,'Location3')

Join the source tables based on ‘DEPT_ID’ column and load the source data into target table “EMP_TARGET”.

EMP_TARGET:-

CREATE TABLE EMP_TARGET (ID INT,NAME VARCHAR(20),DEPT_ID INT,LOC VARCHAR(20))

Package creation:

• Open BIDS.
• F
  rom Control flow items, drag and drop “Data flow task” into designer surface.
• From Data flow items, drag and drop tow “OLEDB Sources” into designer surface (Firs one is for “EMP Table” second one for “DEPT Table”) 
• Map the "OLEDB Sources" with source tables.
• We need to sort the source data based on ‘DEPT_ID’ column because “Merge Join” transformation accepts only sorted data sets as input.
• We can sort the source data in two ways.
  o   Using Sort transformation.
  o   Using OLEDB source “Advanced Editor”
• In this example I used ‘OLEDB Source Advanced Editor’ to sort the source data sets.
• From Data flow items, drag and drop the “Merge Join” transformation into designer surface.

• Select ‘OLEDB source’ (EMP) green arrow and connect to the transformation. Then “Input Output Selection” Window will open. In that window, Select “Merge Join Left Input” from the “Input:” drop down list.

• Select second ‘OLEDB Source’ (DEPT) green arrow and connect to the transformation.

• Right click on “Merge Join” and select “Edit” option.

• Select ‘Join type:’ as Inner join and select the required columns and click “OK” button.

• From ‘Data flow’ items, drag and drop “OLEDB Destination” into Designer surface.
• Connect the ‘Merge Join’ transformation green arrow to ‘OLEDB Destination’.
• Map the ‘OLEDB Destination’ to the target table and execute the package.

Target table Output: