Automate package creation in SSIS

TechNovation
TechNovation used Ask the Experts™
on
Hello,

Building extraction packages in SSIS are time consuming and I'm looking for a way to automate their creation, any idea on how I can do it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
If the extraction is simply mapping one data source to another, you can do it by using Export/Import wizard by right clicking the database node in Management Studio and in the last step save the SSIS package instead of just executing it immediately.

Author

Commented:
The package actually involves:
- extraction source (changes: requires a variable?)
- split of table based on length of fields
- sort
- Merge
- destination sources (change)

The SSIS package to be run should then be the output / product of such an automation
This is very doable, but will require some .NET programming if you want to go the pure route. However, since the SSIS package is just an XML file (.dtsx), you could simplify the process by creating a template package then a routine that will replace the variable values (SQL, source, destination, columns, datatypes, lengths, etc) in the package file. What makes the simplified plan work is if you are doing repetitive tasks, with the same tasks.
As far as making your package itself dynamic, just wont work. As you suggested above, the output of this automation would be to produce a new SSIS package.
Good Luck,
HoggZilla
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
HoggZilla, the source has unfortunately everytime different table structure (different fields..), how complex is the .Net Programming route, what does it take in how do I do it? thank you
Top Expert 2006
Commented:
Since you have business logic involved, you will have to program it somewhere. You can either use the regular GUI tool to create SSIS or use programming model to create it. Either way there is no "automated" way to create it. IMO, programming model would be way too complex and time consuming unless you have very large number of very similar SSIS to be created.
Top Expert 2012
Commented:
This should give you an idea of the complexity of what you are asking:
Creating packages in code - Flat File Source to OLE-DB Destination (SQL Server)
http://www.sqlis.com/post/Flat-File-To-Sql.aspx

Note this is just a package with one task.

Author

Commented:
Is there a way to do it with XML?
For sure you can look at the details using the XML datatype in SQL. Here are a couple of resources you might find useful.
SSIS Programming Basic
SSIS XML

Author

Commented:
There are some useful hints that should remain available, I wouldn't recommend deletion. I wilkl follow up with experts.
Top Expert 2012

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial