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