Dynamic Column Mapping in SSIS : Part 1

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…

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SSIS (SQL Server Integration Service). Bookmark the permalink.

19 Responses to Dynamic Column Mapping in SSIS : Part 1

  1. Dominique says:

    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

  2. Dominique says:

    By retyping the names it seems picking it now … sorry!!
    Next:
    Output0Buffer does not exist in the current context…
    Why this error?

  3. Dominique says:

    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 …

  4. Zahid says:

    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.

  5. Leandro Lemos says:

    Nice post, but nothing dynamic. Manaualy created the output columns and manually mapped to the destination.

    • Zahid says:

      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.

  6. girishek says:

    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

  7. Erik Leung says:

    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.

    • Zahid says:

      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.

      • Erik Leung says:

        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.

  8. Abhishek says:

    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

  9. Zahid says:

    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.

  10. Ernesto says:

    Hello Zahid, what if the destination is Excel file, will work in that scenario?

    Tahnks

  11. is there any solution in script component. says:

    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

    • Zahid says:

      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.

  12. Rafiq says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s