Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS dynamic mapping of columns


SSIS dynamic mapping of columns

Author
Message
chad.bindl
chad.bindl
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 57
Hello AMTZVA,

For sql 2000, you can use vbscript to manipulate the dts object model. I've used this technique to generate dts packages for 100s of tables at a time. I used the excel vb environment to write the code, then execute it like a macro. Then this code can be easily ported to a custom vb application.

This link should get up started:

http://msdn.microsoft.com/en-us/library/aa176227%28v=sql.80%29.aspx

You can learn more about the object model by setting breakpoints and viewing objects in debug mode.

Hope this helps,

Chad
kap_gemini
kap_gemini
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 199
Great post..Is it possible to share the ssis package. I have the same requirment and would like to experiment with it.

In our case source could be Flat files/RDBMs

KApil



naveed.khawaja
naveed.khawaja
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 150
I don't have a copy of the package available now, but do check out the images I posted in one of the replies to this thread. Along with the original explanation, they should give you enough information to build the package quite quickly.
kap_gemini
kap_gemini
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 199
thanks, some kind of code would be helpful. Also in my case even Column list is not defined at design time. So I can have 5 column for say Table1 which needs to be mappped to Destination table (DTable1 say), for next run it could be 4 columns. So i am planning to put the intermidate schema first and then put data into destination. Please suggest



clementhuge
clementhuge
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 301
You can use the data flow plus component from cosyrock.
If you only use on BIDS (and not on sql server agent) it is free. You can then do true ETL stuff with a for each loop to each table).
the only thing first is to create the table structure on SQL server or Oracle.

There is a nice small turorial here:
http://www.cozyroc.com/ssis/data-flow-task

Clement
bill 88428
bill 88428
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 61
Hi,

I resorted to this in the end (INSERT .. OPENQUERY()) although there are some third party custom controls which handle dynamic column metadata.

Seems like a bit of an omission from SSIS as judging from the hundreds of posts on the web a lot of people want to do this and end up becoming rather frustrated.

Thanks for your post.
corey lawson
corey lawson
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 546
SSIS column mappings must be done in "design time". We humans do this manually in SSIS. To do it programmatically means manipulating a template SSIS pkg and then executing that...

Here's a good URL to look at to manipulate SSIS packages in a ScriptTask (as opposed to a ScriptComponent in a DataFlow):

http://bifuture.blogspot.com/2011/01/ssis-adding-derived-column-to-ssis.html

, or use the EzAPI libraries to generate a new package object in memory, too... EzAPI doesn't work so well with manipulating existing packages as there is some kludge code you'll need to do to strip out all the "decoration" XML in the .dtsx file that BIDS puts in a package.

Hennie's got a couple of other full examples, too. To me, this was all "it just works" code, as it's just not really documented by Microsoft. I did try doing some of it on my own, especially the column mappings, but got frustrated, and Hennie's code Just Works.

The main problem I have with EzAPI is working with ADO.Net connection managers as opposed to OleDB will require you to write your own code, as that class is set up as a Virtual class... but EzAPI does work from a regular .Net C#/VB.Net app...

For doing this in a Script Task, you'll need to find and make available the appropriate versions of the Microsoft.SqlServer.DtsRuntimeWrap & Microsoft.SqlServer.DtsPipelineWrap DLLs. For BIDS 2008 using 32-bit ODBC drivers, I made copies of them into c:\Windows\Microsoft.Net\Framework\v2.0.50727, then I could make references to them... They're a bit easier to find/use in BIDS 2010... You can navigate to them in their original locations, but the UI won't let you add them from where they're initially at...

CozyRoc offers a set of ScriptComponents that they say can do dynamic column mappings at run-time as well... The DtsRuntimeWrap & DtsPipelineWrap DLLs are properly linked and usable in ScriptComponents, though...
msbi.sql
msbi.sql
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Please refer this blog

http://ssisinterviewquestionsmsbisupport.blogspot.com/2013/09/dynamically-load-no-of-tables-in-ssis.html
msbi.sql
msbi.sql
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Hi Please refer,
http://ssisinterviewquestionsmsbisupport.blogspot.com/2013/09/dynamically-load-no-of-tables-in-ssis.html
bill 88428
bill 88428
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 61
Hi,

Correct me If I am wrong but this appears to be specifying the column details in an xml file and then this is being used for the table load.

This doesn't appear to be dynamic column mappings. I simply want to copy one table from one instance to another instance in a for loop and not have to specify column mappings each time.

Have I simply ms-read the post? Do I need to read it in more detail?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search