none
Dynamic Column Mapping in SSIS??

    Question

  • Dynamic Column Mapping in SSIS??

    I Want to Map cloumn at run time. I Will Provide Mapping in SQL Table(Source & Destination)

    Anybody can Give me Code Snippest to do it in SSIS????

    Thanks in Advance

    Friday, August 26, 2011 8:09 AM

All replies

  • SSIS DFT doesn't support dynamic metadata. for this you have to write your code in Script task 

    or I think you can try with EST (Exceute SQL Task)

     


    Let us TRY this | Mail me

    My Blog :: http://quest4gen.blogspot.com/
    • Proposed as answer by Tom Phillips Monday, September 23, 2013 9:50 PM
    Friday, August 26, 2011 8:34 AM
  • Dynamic Column Mapping in SSIS??

    I Want to Map cloumn at run time. I Will Provide Mapping in SQL Table(Source & Destination)

    Anybody can Give me Code Snippest to do it in SSIS????

    Thanks in Advance

    Nope not possible

    Only possible way would be create package programatically and there use your mapping table (Source and Destiantion ) as you have already done then run the package.

    Or else put ur logic in sp that would generate dynamic sql and do the ETL for you ...


    Hope that helps ... Kunal
    Friday, August 26, 2011 8:45 AM
  • thanks for reply kunal

    but let me know how to create package programatically with code snippest & where i can write that code????

    Friday, August 26, 2011 9:11 AM
  • Hi krunal,

    I was able to perform dynamic data load / mapping using scripting in .NET (building select and inserts) . There is some cool feature I introduced into this which is error handling or data type validations with error column detection .Please find my post here about this : http://deepaksqlmsbusinessintelligence.blogspot.com/2011/08/dynamic-data-load-in-script-task.html . I shared the entire source code as well as the test files as well. It is my own code:-)


    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Saturday, August 27, 2011 7:45 PM
  • Dynamic Column Mapping in SSIS??

    I Want to Map cloumn at run time. I Will Provide Mapping in SQL Table(Source & Destination)

    Anybody can Give me Code Snippest to do it in SSIS????

    Thanks in Advance

    Hi,

    If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and it supports the exact same functionality you are looking for - dynamic data flows at runtime. No programming required.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Saturday, August 27, 2011 11:59 PM
  • As others explained the SSIS Data Flow task doesn't support dynamic metadata,

    and you should use other methods to transfer data,

    It will be better if you let us know what is your source and destination types? both sql server? or excel to sql server? ...?

    there are some other methods which can be done with Execute SQL Task with commands like Insert into desttable .... select ... from sourcetable, or BCP or OpenRowSet ....

     


    http://www.rad.pasfu.com
    Sunday, August 28, 2011 10:50 AM
    Moderator
  • Thanks for reply

     

    See i Want Source as SQL & my destination will be CRM Using Web Service so i will use Script Component but i want all coding stuff to be done in script component & column mapping will be define in sql table from that it should apply column mapping for source & destination

    now any body please help how i can achieve it if yes then please provide code snippest

     

    Thanks in Advance

    Monday, August 29, 2011 7:50 AM
  • krunal,

    Try SSIS api programming where the data flow task can be programmatically built wherein source and destinations can be connected : Check this code : http://msdn.microsoft.com/en-us/library/ms136086.aspx#Y310.

     

    As you say , mapping is available in SQL table, the code above could possibly be customized to get the column collection (mapping stored in table) and be used . If the code is observed at the end  when the path is created :

     

       // Create the path.
       IDTSPath100 path = dataFlowTask.PathCollection.New();
       path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
        destination.InputCollection[0]);
    
    

    The output and input collections can be fetched from your table and use them here. This needs an idea on SSIS api programming.


    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/
    Monday, August 29, 2011 8:06 AM
  • HI

    You   can use this link to acheive dynamic mapping

    http://ssisinterviewquestionsmsbisupport.blogspot.com/2013/09/dynamically-load-no-of-tables-in-ssis.html

    

    Tuesday, September 17, 2013 7:20 AM
  • One approach is to write an INSERT SQL statement and using an Execute SQL task instead of a Data Flow task.  This is not your exact scenario, but the moving parts are there to address your scenario.  You just have to adapt for your situation and use an Execute SQL task instead of a Data Flow task.

    http://dataqueen.unlimitedviz.com/2013/09/loop-through-dynamic-sql-statements-in-ssis/


    Martina White

    Monday, September 23, 2013 8:21 PM