Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stusco
Starting Member
1 Post |
 Posted - 2011-03-09 : 21:12:12
|
| Hi,My client is attempting to migrate their existing database / datawarehouse environment from SQL Server onto Netezza. The process they have been building to date involves using SSIS to export data from a specified list of SQL Server tables (i.e. not every table in the database is in scope) into text files and then from each text file into a Netezza external table. Although this process does seem a little clunky, I have been reliably informed that this is the only way that the data migration will work.There are many hundreds of tables across about 70 databases that need to be migrated and the BI guy here has more or less been building a package per database, each package containing many data flow tasks and each data flow task containing many OLE Source components mapped to Text File destinations (essentially one source to target map per source table). Understandably, this has been a very, very time consuming process so far and the project is now running behind schedule!I have been brought in to help them get the project back on schedule but feel that I can add more value by simplifying this process and making it more dynamic. To start with, I have created a Master Scope table that contains all of the associated database names, schema names and table names (from INFORMATION_SCHEMA) and there is a flag against each table that indicates whether or not the table is in scope for migration. Using a Foreach Loop container and the ADO Enumerator, we are able to loop through the Master Scope table to create a dynamic select statement using INFORMATION_SCHEMA (again) to retrieve the relevant columns. However this is where my progress comes to a grinding halt....I was hoping that I would then be able to dynamically recreate the destination text file during each iteration of the Foreach Loop container but sadly SSIS is not able to dynamically recreate metadata!I have some vague ideas on how I might get round this but I wondered whether anyone else has a funky solution that they are willing to share. Alternatively, and better still, if anyone has performed this sort of migration before and has some suggestions for a different approach altogether then I am only too happy to listen to this too.Thanks,Stuart |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-03-10 : 00:13:59
|
disclaimer: I have never done anything with SSIS.Could you put a call out to a process in the foreach loop, with args coming from the enumerator? then you could call bcp out/queryout from SSIS. :)
elsasoft.org |
 |
|
|
LeoH
Starting Member
2 Posts |
Posted - 2011-10-05 : 00:08:58
|
| I work for a Medical company and we just completed a project exporting data from SQL Server and Oracle into Netezza every night. We use DBCrane, which streams data from SQL server into Netezza directly, eliminating the need of creating text files. Its configuration is in an XML file, which can be edited manually or from GUI.http://www.bronzeage.us/downloads.html |
|
|
|
|
|
|
|
|
Dynamic Flat File Creation / SQL Server to Netezza