SQL Server vs SSIS Data Types

SQL server	SSIS
smallint	two-byte signed intger (DT_I2)
int	four-byte signed integer (DT_I4)
bigint	eight-byte signed integer (DT_I8)
float	double-precision float (DT_R8)
char,varchar	string (DT_STR)
nchar,nvarchar	unicode string (DT_WSTR)
decimal,numeric	numeric(DT_NUMERIC)
decimal	decimal (DT_DECIMAL)
smallmoney,money	currency (DT_CY)
date	database date (DT_DBDATE)
datetime	database timestamp (DT_DBTIMESTAMP)

 
For More Information click on below link:

• SSIS Expressions

Get Source File Path From a SQL Table and Process the files in ssis

          One folder is having multiple text files, in those i need to load few files into a table. Required file paths are available in a SQL table. In the below example, i am going to explain how to read the file paths from a table.

Example:
Source Files:

Required file path table:
CREATE TABLE SRC_FILE_PATH
(
SNUM INT
,FILE_PATH VARCHAR(500)
)

INSERT INTO SRC_FILE_PATH VALUES (1,'D:\Programs\Forum\Source\Source\SourceFile1.txt')
INSERT INTO SRC_FILE_PATH VALUES (2,'D:\Programs\Forum\Source\Source\SourceFile2.txt')

Target table:
CREATE TABLE TGT_DATA
(
ID INT
,NAME VARCHAR(30)
,PRICE NUMERIC(16,4)
,DESCRIPTION VARCHAR(50)
)

Package Creation:

• Create a variable with the name "SrcFileSet" and data type is "Object".
• Create one more variable with the name "SrcFilePath" and data type is 'String'.

• Drag and drop 'Execute SQL Task' and edit.
• In "General" section give connection (fro SRC_FILE_PATH)
• Give the SQL command (SELECT FILE_PATH FROM SRC_FILE_PATH)
• Select "Full result set" option from

• Go to "Result Set" Section and click on "Add" button.
• Give "Result Name" as '0'
• Select "SrcFileSet" variable from "Variable Name" drop down list.

• Drag and drop "Foreach Loop Container". Connect the 'Execute SQL Task' to 'Froeach Loop container'

• Edit the 'Foreach Loop Container'.
• Go to "Collection" section.
• Select 'Foreach ADO Enumerator' from 'Enumerator' drop down list.

• 'Enumerator configuration' section will enable.
• Select 'SrcFileSet' variable from "ADO Object source variable" drop down list.
• Select "Row in the first table" radio button.

• Go to 'Variable Mappings' section and select 'SrcFilePath' variable from "Variable" drop down list.

• Click on "OK" button.
• Drag and drop the 'Data Flow Task' into 'Foreach Loop Container' from Toolbox.
• Configure any one of Source file to Target table.

• In Control flow, 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 "SrcFilePath" 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" may come. Because "SourceFilePath" variable value will come during 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.

 

Target table data after ETL Run:
 

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.