Using variables in SSIS packages

Requirement:
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

FilePath: Any of the file name from the source excel folder
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

5. Create a excel connection manager and name it as “Excel”

6. Create a connection manager for SQL Server and name it as “SQLServer”. Now we must be having three connection managers

7. For the connection manager “ExcelSchema” configure the expression “Server Name” for using the variable “FilePath”

8. Same with the connection Excel as below

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
BIDS
SSMS
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”
Or
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 Execution:
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.

Second Execution:

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.