SSIS dynamic mapping of columns

Question

SSIS dynamic mapping of columns

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

vithasun Mr or Mrs. 500 Points: 538 More actions · Answer 1

hello Grasshopper ,

I think I have similar kind of task to do.

Actually I have several .txt files(with different number of columns- textfile1 contains about 10 columns and textfile2 contains about 20 columns and textfile3 contains 5 columns and so on).

I need to take these text files as source and send these text files data into sql server destination(temportary tables/tables). But the thing is I need to use single flatfile source and single sql server/oledb destination.

I tried alot and reached at this stage.

I took one for each loop container and configured and created a variable to store .txt filenames.

And in the data flow task, Kept one flat file source and one oledb destination.

And execute the package. now it is reading all the text files but the problem I am getting is, as my text files have different number of columns mapping i am unable to do dynamically.

I try using script task to do it. But i am not able to do that.

As you gave the solution to sql server to oracle. I hope you can surely help me.

I am really need your help.

Thanks alot.

vena.

brainy SSCrazy Points: 2445 More actions · Answer 2

Hi Vena

Please check this link

http://munishbansal.wordpress.com/2009/06/09/dynamic-columns-mapping-%E2%80%93-script-component-as-destination-ssis/

you will get lot information on dynamic mapping of columns.

I hope this will help.

VickyWinner SSC Enthusiast Points: 165 More actions · Answer 3

Hi

Your post is really helpful. But is there a way around to use this example without creating linked server object? Any alternate would be helpful.

Thanks In Advance.

Kiran Chennupati-446386 Valued Member Points: 65 More actions · Answer 4

Hi Naveed,

Can you send me the example of this code to my email address kiran.nalluri@ingenix.com

Apprecite your help

Thanks!

Kiran.

naveed.khawaja Mr or Mrs. 500 Points: 548 More actions · Answer 5

Hi Kiran

I don't have the code separately I'm afraid. The thread should contain all the information you need though. It really doesn't take long to create, since there are only a few different tasks you need. Happy to help if you have a specific issue though.

Naveed

chad.bindl Valued Member Points: 61 More actions · Answer 6

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 Old Hand Points: 317 More actions · Answer 7

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 Mr or Mrs. 500 Points: 548 More actions · Answer 8

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 Old Hand Points: 317 More actions · Answer 9

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 SSC-Addicted Points: 455 More actions · Answer 10

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 SSC Enthusiast Points: 100 More actions · Answer 11

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 Hall of Fame Points: 3773 More actions · Answer 12

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 SSC Enthusiast Points: 146 More actions · Answer 13

Please refer this blog

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

msbi.sql SSC Enthusiast Points: 146 More actions · Answer 14

Hi Please refer,

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

bill 88428 SSC Enthusiast Points: 100 More actions · Answer 15

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?