Dynamically Map source and Destination table in SSIS Package
Hi I came across a scenario where I have to create a ssis package which Dynamically Map source and destination table and load data. I have 30 table which need to be fetch from source on the basis of date column .For Data retrial my query is simple "SELECT * FROM TABLENAME" ; My tables column are different. Please help me on this. Regards Abhishek P Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)
August 7th, 2012 1:00am

In the Script Task you can get the name of the table and columns probably querying sys.columns table based on WHERE object_id=(object_id('tblname')) Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2012 2:09am

Thanks Sir for feed back but its not like that this is dynamically changing of source and destination table. Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question) even if you can get the source, destination dynamically set you may have issues with mapping the fields in between. you can think on dynamically creating everything :http://consultingblogs.emc.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx regards joon
August 11th, 2012 3:12am

if your source tables are in same database and destination is same for all, you can prepare whole set of source statement in the sql task Or using some sp and then pass the generic columns for mapping with destination. select col1 as 'A', col2 as 'B' from Table1 union select col3 as 'A', col4 as 'B' from Table2 union select col5 as 'A', col6 as 'B' from Table3 union select col7 as 'A', col8 as 'B' from Table4 regards joon
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2012 3:54am

Thanks Sir I have used Script task and execute sql task and completed my task. Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)
August 11th, 2012 4:55am

Thanks Sir for feed back Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2012 5:17am

You can either: 1 - Use SSMS "Import and Export Wizard to create a package for those 30 tables on the fly. It takes only 5 minutes to do that and modify it to your needs. 2 - OR, use staging tables to stage the data and use SPs have the implementation logic and run them from the SSIS package 3 - OR, there is no direct way to change DFT destination field settings from within the package but you can modify it from a parent package before running the child package. here is blog that can give you a heads up: http://sqldb.wordpress.com/2006/06/22/self-modifying-packages-in-ssis/ Hope this helps- Please mark the post as answered if it answers your question
August 11th, 2012 6:16am

Hello, Check out the links below. It may help you. http://wikiprogrammer.wordpress.com/2011/04/19/dynamic-column-mapping-in-ssis-part-2-using-dts-variable/ munishbansal.wordpress.com/2009/06/09/dynamic-columns-mapping--script-component-as-destination-ssis/ http://www.sqlservercentral.com/Forums/Topic628424-147-1.aspx
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2012 6:21am

Hi Abhishek , 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 dynamic columns metadata at runtime. You can handle all 30 tables using only one Data Flow Task Plus running inside For Each Loop (FEL) container. No programming skills are required.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
August 11th, 2012 1:28pm

Thanks Sir for feed back but its not like that this is dynamically changing of source and destination table.Abhishek Parihar BI Consultant (Please mark the post as answered if it answers your question)
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2012 12:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics