Execute Package Task

          Using "Execute Package Task", we can call other SSIS packages in to current package.Current package we can call it as 'Parent package', other packages we can call as 'Child packages'. If we run the parent package, child packages also will execute along with parent.

Example:

• In 'Control Flow', drag and drop "Execute Package Task" from Toolbox.

• Edit the "Execute Package Task"
• Go to "Packages" section.
• Select SSIS package location from the "Location" drop down list.

          My SSIS package is available in "File System", so i am going to select "File System" option.

• Select "New Connection" Option from "Connection" drop down list.

• "File Connection Manager Editor" Window will open.
• Browse the Required SSIS package.

• Click on 'File Connection Manager Editor' "OK" button.

 

•  Click on "Execute Package Task Editor" OK button.
• Package is ready to execute.

FTP Task

            Using FTP Task we can download or upload data files and manage directories in FTP Server.

 

 Example:

            In this example i am going to explain how to use FTP Task.

• Drag and drop FTP Task from Toolbox.
• Edit the FTP Task.
• In General section, Select New FTP Connection from "FtpConnection" drop down list.

• "FTP Connection Manager Editor" window will open. Give the "Server Name, User Name and Password"

• Click on "Test Connection" Button to check connection details.
• It will prompt connection status, click on "OK" Button.

• Click on "FTP Connection Manager Editor" window "OK" button. 
• FTP Connection Manager creation is completed.

• Go to "File Transfer" section.

• Select required Operation from "Operation" drop down list.

• Browse local path using "LocalPath" drop down list.

 

•  Same way browse FTP Path in "Remote Parameters" section.

• Click on "OK" Button.
• Package is ready to execute.

Avoid Sort Transformation in SSIS

           If we use Sort transformation for large data sets, performance issues will come. So we need to avoid sort transformation.

Example:

• Edit the source (excel or OLEDB source) and change the “Data access mode:” to “SQL Command”.

• Writ the select statement for source table with order by (on required fields).

• Click “OK”.
• Right click on the source and select “Show Advanced Editor” option.

• “Advanced Editor for OLEDB Source” window will open. Click on “Input and Output Properties” option.

• In the “Inputs and Outputs:” section, select the “OLEDB Source Output”.

• Right side of the window “Common Properties” will enable.

• Set “IsSorted” property as true.

• In “Inputs and outputs:” section expand the “OLEDB Source Output” list.

• Expand the “Output Columns” list. And select ID column because we need to sort the source data based on ID column.

• ID column “common Properties” list set the “SortKeyPosition” as “1”.

•  If you want to add one more column in sorted list. Select required column and set the "SortKeyPosition" value "2".
• Click “OK” button to complete the sorting.
• One sorted data set is ready.
•  Same way we can prepare remaining required sorted data sets, without using sort transformation.

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