Another dynamic SSIS question

Question

I'm seeking just a simple yes or now as to whether this can be done, and if so - a gentle nudge as to what approach to take.

The business problem is that our organization has used SQL Server as the data warehouse after a pretty intense ETL process that runs overnight. We recently acquired a Netezza appliance to interfact with Siebel (or Oracle BI), and I'm trying to create a process by which there nightly copies of the DW database in SQL Server over to Netezza.  All of this is simple to manually, but the requirements call me for me to create a metadata table of tables that are to be included in nightly and intra-day hourly updates.  This metadata table has the name of each table, the key data field for gettin recent transactions from Fact tables for hourly updates.

Based on input from our Netezza advisor, Netezza can handle up to six syncronous packages running at at a time.  For each scheduled task, the package would look at the metadata table for a list of tables, and use sys.all_columns to build up the package details.  Fortunately, the name of the tables and fields are identitcal from SQL Server to Netezza, and I've created a .Net page that allows developers to specify new tables they've created and the corresponding key date field that creates the Netezza DDL language to create the table and make data type translations.

I keep reading that this type of dynamic SSIS development is not possible, but there is sample code that comes close to what I need.

Any help you can provide on the general approach to doing this would be greatly appreciated.

Cheers,

S.

Answer 1

I am trying to comprehend the required. Do you want to build your SSIS dynamically and then execute each time?

---

Arthur My Blog

Answer 2

You will not be able to do this from the basic tasks and transforms provided in ssis.

You would have to get into making the package dynamically.

---

Tweet me..

Answer 3

Yes,

In my case we have close to 100 tables - a dozen fact tables and the rest are dimension tables.  For change control purposes, I've been asked to make this process run six packages at a time - grabbing the first six tables that are listed in the MetaData table.  The data flow task starts with a sql task to truncate the table in Netezza and then copy all rows from the source to the destination.  Both source and destination field names are named exactly the same.

As soon as one of the packages completes the copy of its table, it looks for the next table in the MetaData table of tables that has not been processed or is being processed and dynamically creates the SSIS dataflow based on the sys.all_columns for that table.  So, the name of the table will be assigned to a variable, and then the package is created by looping through a dataset within sys.all_columns to create the package mapping.

Even if this is not possible within the standard SSIS basic tasks, I'm just looking for advice on how to create a custom task to accomplish this - and just a minimal amount of direction or example of getting this done with a basic table.

But the first question is whether this can be done at all.

Hope that helps clarify.

Thanks again,

s.

Answer 4

...

For change control purposes, I've been asked to make this process run six packages at a time - grabbing the first six tables

...

What change control has to do with this?

Why don't you create a package that loops through all the tables in your metadata table one at a time?

---

Arthur My Blog

Answer 5

We've been told that Netezza's sweet spot for importing data comes at six synchronized tasks being processed - no more, no less.  I mentioned change control, because it would be easiest for me just to develop the packages manually and even balance them for load.  But if a developer adds or alters a table, those changes need to be addressed during the change control process programmatically through this MetaData table of tables.

If you have an example of how to loop through all the tables in a metadata table, that certainly would get me started in the right direction.  I've yet to find any code or examples in any article on how to do just that.

cheers,

s.

Answer 6

Sure,

My guess you need to loop through the resultset of a query against the Metadata table.

Here is such example: http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

Perhaps you need to also limit the number of loops that can be done this way: http://www.sqlyoga.com/2009/11/sql-server-ssis-for-loop-container.html

---

Arthur My Blog

Answer 7

Hi,

I am in a similar situation with migration to Netezza from MSSQLServer and Oracle. I am looking to develop an SSIS dynamic package solution. How did you get on with this? I'm keen to hear whether this is worth exploring.

any help would be great.

thanks

nn

Answer 8

Hi,

I am in a similar situation with migration to Netezza from MSSQLServer and Oracle. I am looking to develop an SSIS dynamic package solution. How did you get on with this? I'm keen to hear whether this is worth exploring.

any help would be great.

thanks

nn

Check this post. It requires installation of the commercial CozyRoc SSIS+ library.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/