Import Data from Excel to SQL Using SSIS 2012 Dynamic Configuration

Package 1: Import Excel Data to Database Table

Step 1: Create Sample Excel data same as below:

Step 2: Create folder name “ImportExcel” and copy the SampleData file here.

Step 3: Open SQL Server Management Studio and run the below script to create database and its tables.

create database ImportExcel

Use ImportExcel
Go

create table EMP_A(Id Int,Name Nvarchar(500),Dept varchar(10) default 'IT')
create table EMP_B(Id Int,Name Nvarchar(500),Dept varchar(10) default 'HR')

Step 4: Create SSIS project and name as “ImportExcel”.

Step 5: Drag one sequence container and name it as “ImportExcel” as below:

Step 6: Drag one Dataflow task to the sequence Container.

Step 7: Right click on package and click on variables and click on add variable.

Step 8: Since we want to run this package as dynamic... please go to dataflow task properties and make delayvalidation property as “True”.

Step 9: Double click on Dataflow task and bring Excel source and make delay validation as “True” (ValidateExternalMetaData=”false”).

Step 10: Right click on “Excelsource” and click on Edit.

Step 11: Click on new and browse the Excel path and click on OK.

Step 12: Select like below and click on ok.

Step 13: Now our Source connection is ready and developed.

Step 14: We just want to make our ExcelSource Connection as Dynamic.

Right click on “Excel Connection Manger” and click on properties -> Click on Expressions.

Step 15: Click on property dropdown and select ExcelFilepath.

Step 16: Browse Expression button and select variable as below and click on ok.

Step 17: We are done with source and now we need to configure Destination as below.

Drag and drop “OLEDB Destination “ and make a connection to source and destination as below.

Step 18: Right click on Destination and click on Properties and make “validateExternalMetadata” as False.

Step 19: Right click on Destination and click on edit.

Step 20: We are done with package. Before running the package, please make the below changes as Excel adapter does not support 64bit.

Right click on project and click on properties and select Debugging and make Run64bitRuntime as false.

Step 21: Save & run the package.

Step 22: Check whether data has been populated in database tables or not.

Step 23: Next step is we need to create dynamic configuration, to achieve this please follow the below steps:

Click on SSIS Menu->packageConfigurations->EnablePackageConfigurations-> click on Add

Step 24: Next->Add XML ConfigurationFile->Browse select path and click on Next.

Step 25: And select variables which you want to make as dynamic and click on next->Finish->Close.

Step 26: Now, we are able to see file called “ImportExcelProjConfig.dtsconfig”.

Step 27: Now, you can change this file for dynamic purpose.

For testing, we just change the sample data path and destination table path as below:

SampleData : D:\practice\SSIS\B\SampleData.xlsx
Table : EMP_B

Step 28: Save and run the package to test the results: