June 16, 2011 at 10:53 am
I've read several posts including this one http://www.sqlservercentral.com/Forums/Topic1083821-364-1.aspx?Highlight=generate+output+columns that essentially ask the question "Is there a way to dynamically generate output columns from a source." Based on the answers in this forum the answer is "no".
Let's ponder that we as developers regularly import data to SQL tables given new fixed-length flat file layouts that when parsed have 100+ columns. Given a table that defines a field layout and more, you could build a procedure to generate code for yourself that creates the required SQL table and another procedure that generates code that can be pasted into a Source Type Script Component, for example, to provide most or all of that script code. However, through the SSIS design UI you still have to manually add EACH output column, set its data type, etc.
Is there any third party software that can generate an SSIS package (.dtsx file) that addresses this? It would be a real time saver to have the guts of a package to start from especially with so many columns to define. Or, do you have a suggestion based on something you've built?
June 16, 2011 at 12:06 pm
billj-705548 (6/16/2011)
I've read several posts including this one http://www.sqlservercentral.com/Forums/Topic1083821-364-1.aspx?Highlight=generate+output+columns that essentially ask the question "Is there a way to dynamically generate output columns from a source." Based on the answers in this forum the answer is "no".Let's ponder that we as developers regularly import data to SQL tables given new fixed-length flat file layouts that when parsed have 100+ columns. Given a table that defines a field layout and more, you could build a procedure to generate code for yourself that creates the required SQL table and another procedure that generates code that can be pasted into a Source Type Script Component, for example, to provide most or all of that script code. However, through the SSIS design UI you still have to manually add EACH output column, set its data type, etc.
Is there any third party software that can generate an SSIS package (.dtsx file) that addresses this? It would be a real time saver to have the guts of a package to start from especially with so many columns to define. Or, do you have a suggestion based on something you've built?
SSIS packages can be generated programatically. How are you with C# or VB.net?
I implemented something where a disk-resident DTS package (SQL 2000) was loaded into memory inside a VB6 app leveraging the DTS object model. The DTS package's properties were then modified programmatically and then it was executed directly from the VB6 memory space. This made use of a DTS-template-per-file-format and all that was changed were the source and destination properties. It was not taken quite as far as you want to take it with SSIS where a complete package was created from scratch, but all the options were available in the DTS object model in terms of creating/mapping new columns programmatically.
I have not explored whether that technique is still relevant with SSIS packages or how that has changed, but there is a programming model there for exploration and I would expect most if not all of those options to have been carried forward, as well as additional fucntionality.
http://msdn.microsoft.com/en-us/library/ms345167.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 12:11 pm
You may want to look at BIML, as that may offer the flexibility you're looking for. Check out John Welch's blog (http://www.agilebi.com/jwelch/[/url]) as he developed it. BIDS Helper[/url], a free tool available on Codeplex, has BIML support in it so you can start playing around with it to see if it fits your needs.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply