Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

SSIS dynamic mapping of columns Expand / Collapse
Author
Message
Posted Thursday, February 3, 2011 9:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 12:57 PM
Points: 1, Visits: 55
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
Post #1058197
Posted Wednesday, August 24, 2011 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:55 AM
Points: 15, Visits: 180
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



Post #1164561
Posted Wednesday, August 24, 2011 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:49 AM
Points: 20, Visits: 145
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.
Post #1164569
Posted Wednesday, August 24, 2011 5:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:55 AM
Points: 15, Visits: 180
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


Post #1164573
Posted Saturday, December 17, 2011 9:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
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
Post #1223487
Posted Friday, March 8, 2013 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 6:29 AM
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.

Post #1428476
Posted Wednesday, August 28, 2013 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:18 PM
Points: 156, Visits: 367
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...

Post #1489361
Posted Tuesday, September 17, 2013 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 4:25 AM
Points: 2, Visits: 1
Please refer this blog

http://ssisinterviewquestionsmsbisupport.blogspot.com/2013/09/dynamically-load-no-of-tables-in-ssis.html
Post #1495434
Posted Tuesday, September 17, 2013 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 4:25 AM
Points: 2, Visits: 1
Hi Please refer,
http://ssisinterviewquestionsmsbisupport.blogspot.com/2013/09/dynamically-load-no-of-tables-in-ssis.html
Post #1495436
Posted Wednesday, September 18, 2013 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 6:29 AM
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?
Post #1495797
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse