SSIS Data Flow Task is strictly meta data bound. Input table’s columns are mapped at design time. Let’s say u have a table (Table_1) where column names (JAN,FEB,MAR) are dynamically changed and u don’t have any control over it.
SELECT * FROM dbo.Table_1
returns
ID JAN FEB MAR
1 100 200 300
2 400 500 600
3 700 800 900
In the next month, it becomes
ID APR MAY JUN
1 100 200 300
2 400 500 600
3 700 800 900
and the Data Flow Task throws error.
Now, I am going to describe one possible solution using Script Component of SSIS. When you double click on the Data Flow Task in BIDS, u will be forwarded to the Data Flow Task design editor. There, in the toolbox, u will find one component called Script Component. So, u drag the component and place it on the design area. After u drop the component, u will be prompted to choose whether u will use the component as “source”, “destination” or “transformation”. Just choose “source” and click “ok”.
Now, u double click on the script component and editor window will open. Now, u go to “Inputs and Outputs”, select “Output Columns” from the right had side area. Then click “Add column” 3 times. You can change the column data type according to your need. Otherwise, there will be data type mismatch when u will read data inside the script and assign data value into the column.
Click on “Script” on the left hand side of the script editor and click on “Edit Script”. Now, the script will be open in new Visual Studio IDE. You will find three functions: PreExecute, PostExecute and CreateNewOutputRows. Inside the PreExecute method, u should define the connection needed to make your query and inside the CreateNewOutputRows method, u should read from the query and map the result set to the output columns of the component. Below is the code snippet:
SqlDataReader Reader;
public override void PreExecute()
{
base.PreExecute();
SqlConnection Conn = new SqlConnection(“Data Source=DACW0077;Initial Catalog=Demo;Integrated Security=SSPI;”);
SqlCommand Cmd = new SqlCommand(“Select * from dbo.Table_1”,Conn);
Conn.Open();
Reader = Cmd.ExecuteReader();
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void CreateNewOutputRows()
{
while (Reader.Read())
{
Output0Buffer.AddRow();
Output0Buffer.Column = Reader.GetInt32(3);
Output0Buffer.Column1 = Reader.GetInt32(2);
Output0Buffer.Column2 = Reader.GetInt32(1);
Output0Buffer.Column3 = Reader.GetInt32(0);
}
}
Now, u r almost ready to shoot. Just drag a Destination component onto the designer. And, map the columns from Output columns of Script component to the destination column. Make sure that the destination columns matches with the source columns. Now, execute the data flow task and have fun…
Zahid's blog on Data Engineering and BI 
Hello,
trying this snippet i am getting an error:
“‘ScriptMain.CreateNewOutputRows()’: no suitable method found to override”
Where is the definition of this method?
Thanks,
Dom
By retyping the names it seems picking it now … sorry!!
Next:
Output0Buffer does not exist in the current context…
Why this error?
Output0Buffer
Apparently the “SynchronousinputID should be set to None to make the output0 available for the script which is not what the documentation I have said “input “input 0” 2063 really confusing …
The default name of the buffer is “Output 0”. If u keep it “Output 0”, u can access the buffer as “Output0Buffer” inside your script. If u rename it to something else, let’s say……”test”, it should be “testBuffer” inside your script. R u using the script component as “Source” or “Destination”? If it is Destination, the default naming convention will change. Did u manage to use the component successfully already? Also, take note that I did not have to change any property/setting in order to access the buffer from inside my script.
Nice post, but nothing dynamic. Manaualy created the output columns and manually mapped to the destination.
Leandro
Here, dynamic means if the source column names change without any notice, the package will be able to handle it and run smoothly. SSIS is strictly meta-data bound and column mapping has to be done at development time. That’s why this type of work-arounds are needed to handle dynamic name changes. I think u probably expected to see “creating packages programmatically” or something like that. You can google for that and there are some useful posts out there.
Hi Zahid,
I need to import my data from excel to my ms sql server 2008 so could to plz send me the script to import data from any excel without specifying excel name,path and columns name or no of columns.
Thanks & Regards,
Girishek
I published a new post on this. Please, visit
https://wikiprogrammer.wordpress.com/2012/02/24/load-excel-file-dynamically-into-database-using-sqlbulkcopy-and-getoledbschematable-in-c/
Hi Zahid,
Nice post, it solved 80% of my current problem. The only thing is that the amount of output columns varies. The CSV source file from my client contains 10 standard columns, but sometimes it may include 2 to 5 additional columns. Should I really create 15 columns for my output and just choose to leave them blank or is there a better way to do it?
Thanks in advance.
Hello Erik
There are other ways, but not a better way. You have to check if the extra columns exits or not. If they don’t, u have to insert a dummy value or null into those output columns.
Thanks for your reply. At the end we decided to use a configuration table that includes all the information about the files and use a script component to process it.
It sounds like a lot of work, but if it gets made it can be generic enough so that hopefully I don’t have to make another ETL package to read CSV files again.
hi zahid
Nice post by you for ssis. I have a query regarding “How to handle dynamically changing soure and destination table in the ssis package ?
E.g Suppose i have 10 table and i want to retrieve data by changing source and table name dynamically……..Please help m eon this.
Regards
Abhishek
Hi Abhishek
When you configure a source in the data flow task, you can choose variable to use as a source. You can read this post to get an idea
https://wikiprogrammer.wordpress.com/2011/04/19/dynamic-column-mapping-in-ssis-part-2-using-dts-variable/
You can use the concept of this post to iterate through different tables provided that their structure remains the same.
Hello Zahid, what if the destination is Excel file, will work in that scenario?
Tahnks
Ya, it should work.
Hi,
I have been asked solution for this.
I have Vendors where i get different excel file (diffrenct structure) from different vendor.
Vendor1 excel file
——————
Name, Address, Age
Vendor2 Excel file
——————
CandidateName, PhysicalAddress, Age
what ever Header Names but meaning is same.
Order of Headers are may vary different…
but i need to map in Sqlserver with correct mapping means i have table like (CandidateName,CandidateAddress,Age)…. Here i need to store correct data columns which are mapped from above two excels.
Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.
Thanks,
Ravikiran
Since the order of your input files’ headers may vary, the only way to deal with your situation is to maintain a mapping/configuration table and use that in script task to map the input columns to output columns properly.
Salaam Zaahid , i cant get this to work in the script component and get many syntax errors
do you perhaps have this example to send me in zip format or perhaps copy the full code into a text file for me?
im using BIDS2008
much appreciated
Rafiq
I want to know if source does not have fix number of columns and destination has drop and recreate strategy the how can we achieve dynamic mapping in SSIS.
USE SOURCE QUERY
——————————-
SELECT * FROM ( SELECT NULL ID , NULL MONTH_ONE, NULL MONTH_TWO, NULL MONTH_THERE
UNION ALL
SELECT * FROM dbo.Table_1) X
WHERE X.ID IS NOT NULL
IF U USE THIS THEN META DATA DONT CHANGE ………