Previously I have discussed the dynamic column mapping using script component. Now, I am going to mention one more way of dynamic column mapping in SSIS. Let’s say, our source table is Table_1:
ID JAN FEB MAR
1 100 200 300
2 400 500 600
3 700 800 900
We want to map its columns dynamically in data flow task.
First, u need to create a DTS package variable called “COMMAND” in your SSIS and the type will be string. The value of the variable should be “Select 1 as Col1,2 as Col2,3 as Col3,4 as Col4”.
Now, drag a data flow task onto the designer. Double Click the task to go to the editor. On the editor, drag one oledb source and double click to edit it. Select the connection, then select the Data Access Mode as “SQL Command From Variable”. Then choose your variable “COMMAND” in variable name. Then, click on the column mapping and click ok.
Now, drag one OleDb destination on the editor of the data flow task. Connect it with the source and double click to edit it. Now, Configure the Destination in usual way. Below is a screen shot:
Now, click on Mappings and it should be like below:
As, u can see the columns from variable “COMMAND” will be working as the source and they will be mapped to the destination table’s columns. After clicking “OK”, go back to the original control flow designer area and drag one script task from the toolbox. Place it before data flow task and connect it with the data flow task.
Double click the script task, set ReadWriteVariable to User variable “COMMAND”. Click on the Edit Script button. Now write some C# code to
1. read the source table columns dynamically/programmatically
2. build an SQL command string using the column names of the source table
3. assign the SQL command string to the variable “COMMAND”.
After all these, u r ready to go. So, execute the package and data will be loaded into the destination. Below is the code snippet for doing the above 3 tasks:
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
SqlConnection Conn = new
SqlConnection(“Data Source=DACW0077;Initial Catalog=Demo;Integrated Security=SSPI;”);
SqlCommand Cmd = new
SqlCommand(“Select top 1* from Table_1”, Conn);
Reader = Cmd.ExecuteReader();
string Col1_Name = Reader.GetName(0); //GET THE COLUMN NAMES
string Col2_Name = Reader.GetName(1); //GET THE COLUMN NAMES
string Col3_Name = Reader.GetName(2); //GET THE COLUMN NAMES
string Col4_Name = Reader.GetName(3); //GET THE COLUMN NAMES
//////BUILD THE SQL COMMAND AND ASSIGN IT TO VARIABLE/////////////////
Dts.Variables[“COMMAND”].Value = “Select “ + Col1_Name + ” AS Col1,” + Col2_Name + ” AS Col2,” +
Col3_Name + ” AS Col3,” + Col4_Name + ” AS Col4 from Table_1″;