Using variables in SSIS packages
We have got a request to design a package which needs to be take care of the below functionality.
1. Every day we get data from a client. There are a bunch of excel files located in a folder
2. Folder location and folder name may vary from day to day
3. Excel file names are vary from day to day
4. Each file may or may not have more than one sheet.
5. Excel sheet names may vary from day to day
6. No change in schema. All files/sheets are follows the same table structure.
7. There is a junior sql expert who can be able to handle the package
Now we need a SSIS package which satisfies all these 7 conditions.
Steps to design a SSIS package using SSIS 2008 R2. In below example we can see using variables in SSIS packages and passing parameter values at runtime.
Using variables in SSIS packages:
1. Create few excel files and then add sheets with different names
Source Folder: D:\SSIS_PKG\Import
Excel File: Division1 with 2 sheets in it (Dept1, Dept2) are having equal schema
Excel File: Division32 with 3 sheets in it (Dept1, Test, Leave) are having same schema
Finally I have created 5 excel files with different names and of course different sheet names too
3. Open BIDS, create a new SSIS project. Create 4 variables as below
FilePattern: Type of File
FolderPath: Source folder where files are located
SheetName: First sheet name of the file mentioned at FilePath
4. Create a new OLD DB Connection and select the provider “Microsoft office 12.0 Access Database Engine OLEDB Provider”. Name it as “ExcelSchema”
Make sure Exce 12.0 (Excel 2007) selecetd in “Extended Properties”. Test the connection
10. Configure the first foreach loop container (ForExcel_Files) as below
11. Configure the first foreach loop container (ForExcel_Sheets) as below
12. Open the Dataflow task and place three tasks. Excel Source, Derived Column and OLEDB Destination
13. Set the excel source properties as below
14. Set the derived column properties as below
15. Set OLEDB destination details as below
16. Now we are good to go with executing the package. Execute the package from BIDS
We can verify it by using querying the table “Emp”. Carefully observe the columns “FilePath” and “SheetName“.
Now we are ready with the SSIS package to import data from excel files to database. But it needs to be customized as every time the variable values are changed.
We must be able to send parameters at run time hence the package configurations should be stored somewhere out of the package. The famous ways are XML file, SQL Server. I’ll choose SQL Server.
Steps to be taken for dynamic SSIS configuration:
1. Right click on control flow area and click on “Package Configurations”
2. Enable package configurations.
3. Add new sql server configuration. Create a new configuration table as below
Click on Next button. Select what are the parameters that we need to send at runtime. I choose variables. Since properties are also plays vital role we need to add only required properties.
SQL Server configuration has been added.
We can also check it from SQL Server as below
5. Lets test the dynamic property of the package by changing the variable values at database.
There are different ways for executing a SSIS package. Below are the few
SQL Agent – Creating a Job / Maintenance Plan
CMD Shell – DTEXEC
CMD Shell – DTEXECUI
Now we try to execute from command prompt.
Package Name: Package.dtsx
Package Location: File System (“D:\SSIS_PKG\Import_Excel_Dynamic\Import_Excel_Dynamic”)
We can execute it from DOS prompt
dtexec /f “D:\SSIS_PKG\Import_Excel_Dynamic\Import_Excel_Dynamic\Package.dtsx”
dtexec /f “D:\SSIS_PKG\Import_Excel_Dynamic\Import_Excel_Dynamic\Package.dtsx” > D:\SSIS_Log.txt
in second method it captures the execution log into a log file specified where as in first method the log displays in dos prompt itself
Passing Parameters at Runtime:
First we will execute the package with the default variable values as below
Now execute the package from DOS Prompt
Check the destination table.
From the table we can find out that all the files are located at “D:\SSIS_PKG\Import\”
Now I am going to change the default values and execute the package again.
Update the values as below
Variable_Name Old_Value New_Value
FolderPath D:\SSIS_PKG\Import\ D:\SSIS_PKG\Dynamic_Test\
FilePath D:\SSIS_PKG\Import\Division1.xlsx D:\SSIS_PKG\Dynamic_Test\Div1.xlsx
SheetName Dept1$ D1$
FilePattern *.xlsx *.xlsx
Update table “SSIS_Configurations” with the new values
Again execute the package from command line and test the output from SSMS.
Verify the columns “FilePath” and “SheetName” carefully. These are pointing to newly updated values at “SSIS_Configurations”
In the same way the variable values can be passed at the runtime when and as required.