March 21, 2012 at 1:40 pm
Hello Guys,
I need to copy tables from 40 different databases. All the databases have schema called version10 and there are multiple tables within this schema. I will have to do this once every week to consolidate data from different database to one central database. I was trying to use integration services and transfer objects wizard. I am stuck with selecting all the objects within a specific schema. I was hoping there would be an option within SSIS for writing a query or something for selecting tables and where I can specify I need all the tables from this schema and then run the package. Anyone has done anything similar to this?
Any help would be great
Thanks,
Nick
March 21, 2012 at 5:25 pm
The thing is that schemas belong to databases, not the other way around.
That said there are ways to query an instance and find any databases with a particular schema but i suspect your databases are on separate instances.
Are the tables within each schema identical? Reliably so?
Are the logins the same too?
If they are you could have a parent/child package pair where you give the parent a list of databases and execute the child on a loop passing the database name each time
and setting the child's connection properties as required on each execution from the parent.
This is a serial solution but could be adapted to suit your requirements.
March 22, 2012 at 8:33 am
Thanks for the reply,
All the databases are on the same instance and list of tables are different in all the databases. The thing I am looking for is lets say I only have one database with one schema V1 and there are 100 tables in that schema V1. I would like to copy the structure and data from all the tables within this schema to another database. The list of tables might change and so I would like to dynamically select all the tables and then copy them to this other database.
Do you think I can do this in SSIS?
Thanks
March 22, 2012 at 5:34 pm
Since SSIS is based on fixed metadata it doesn't normally do 'dynamic' in the way you are looking for.
The whole premise of SSIS is that you map source to destination columns with transformations inbetween as required.
I have heard of people attempting deviations from the design limitations of SSIS, shall we say, and it appears they have been successful:
Doing a google search for "ssis dynamic mapping of columns" reveals quite a bit of material.
There's a post here:
http://qa.sqlservercentral.com/Forums/Topic628424-147-3.aspx
It appears there is a cozyrock component available to support dynamics.
http://www.cozyroc.com/ssis/data-flow-task
This is an example of an implementation of the cozyrock component:
http://informatics.northwestern.edu/blog/edw/2011/08/introducing-etl-assistant/
It might complicate things if you require the solution to create the destination tables as well.
Alternatively you could pursue doing this in SQL.
This example is of dynamically generated MERGE statements but the principle could be adapted to do what you ask:
http://qa.sqlservercentral.com/articles/EDW/77100/
Caution: if using Dynamic SQL make yourself aware of the risks. I don't know much about it personally though.
April 6, 2012 at 4:43 am
It appears there is a cozyrock component available to support dynamics.
http://www.cozyroc.com/ssis/data-flow-task
This is an example of an implementation of the cozyrock component:
http://informatics.northwestern.edu/blog/edw/2011/08/introducing-etl-assistant/
It might complicate things if you require the solution to create the destination tables as well.
Hi - saw this thread and wanted to reply. I'm from Northwestern and the author of the linked example of the cozyroc component. (That component has solved so many headaches for me - kudos to them for writing it)
Doing the dynamic generation of the target table is definitely possible, but is a risk if you don't have "like" systems. Case in point - we're going from Oracle to MSSQL 2008R2, so reviewing data types are a huge part of generating the target table. Numeric(38) to ....? Tinyint? Int? Bigint? We actually have a class that does a few things:
1) reflects on the source table and projects out the OLEDB type for the columns
2) emits the primary key info
3) allows for overriding of projected data types by...
- source system type + OLEDB type (EX: "when I see a OLEDB type X, make it a SQL int")
- source system + OLEDB type (EX: "I know that for this medical system, all NUMERIC(38) should, but default, map to int")
- regular expressions for column names (EX: "cols with ._cd_. in the name are sized to int, while ._id_. are sized to bigint, while all ._flg_. numeric(38) are sized to bit)
- (combinations of all of the above) "For this Oracle-based medical system, make all ._cd_. columns sized to int, except ._big_cd_. which should be sized to bigint")
4) optional index generation
5) applies standard things we want on all tables (EX: for this system, we want DATA_COMPRESSION = ROW, etc.)
MSSQL to MSSQL? No problem. But then SSIS may not be an ideal choice given how many other options you have for replication.
We generate the table DDL for new systems via a web-based UI, but always review them. We could have automated them, but made an executive decision that our data architects will review / profile all new table DDL. We felt there was just too much risk in doing this automatically. The DDL builder is incredibly helpful, but I don't trust it enough with new systems to have it do things without review. The one thing we do automatically is create the staging tables for incremental loads. The single SSIS package we have for dynamic table import has a concept of staging the data and utilizes SMO (http://msdn.microsoft.com/en-us/library/ms162169.aspx) inside of a script task to reflect on the target table and create the staging table for you. It then uses Nick Smith's dynamic merge process (http://qa.sqlservercentral.com/articles/EDW/77100/[/url]) to merge from staging to production. That whole setup / choice is a single click setup for us "yes - stage the data" and does involve dynamic table creation.
I've been terribly lazy about updating that blog post (busy :P), but hope to put out more content soon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply