November 12, 2008 at 6:10 am
Hi All,
SSIS newbie working for a company with zero training budget at present!
I'm hoping to extract data from multiple oracle tables and then load them into SQL Server 2005. I also need to keep the indexes correct.
What would be the best way to achieve this? Could I use a container which somehow iterates through a separate SQL table which holds the oracle table info? And then use a rebuild index task?
Any help appreciated as I foresee a steep learning curve in the next few weeks/(years?)
Thanks,
Wardy. :ermm:
November 13, 2008 at 2:05 am
Would be grateful for any advice. 🙂
November 13, 2008 at 7:07 am
If the tables that you will be importing are going to have a static layout, you can build multiple parallel data flows that convey from table to table without having to iterate. I've not tried dynamic datasource definitions in SSIS, as all of my sources are static structure flat files. The index rebuild sounds like it will work, my best recommendation, if you have the capability, is to dive into it within a development environment and try it.
There are a number of decent sources for SSIS help online other than BOL. I've referred a lot to Jamie Thomson's blog at http://blogs.conchango.com/jamiethomson/, Martin Policht's articles at http://www.databasejournal.com/article.php/1503191, and the SSIS team blog at http://blogs.msdn.com/mattm/default.aspx.
November 13, 2008 at 8:15 am
Thanks Journeyman, I will have a look at the resources you mention.
Trouble is I have over 100 tables to import from Oracle each day into a Warehouse.
I'm already finding other issues aswell. Some of my date fields in these tables are populated with say, 01/12/197, for a date of birth. The import wizard bombs out immediately because of this.
Wardy
November 13, 2008 at 10:07 am
For the poorly formed date fields, you may have to use a Derived Column transformation to trap them, and then either null them out or insert a known "unknown" token date based on whatever your business rules are. I have to do this in my processes as well. If this doesn't offer enough flexibility, you might consider writing a VB script to apply more logic in these cases.
Unfortunately, I've found the import wizard to be inadequate for my needs due to the variations in source data integrity. I use static import definitions based on the known structure of my source data, and handle known problem fields and data type conversions through Derived Columns. I built the first workflow as a template, and then copied the solution and packages and modified them as needed for each of my other sources. I also use a parent package in each solution which calls all of the other packages to control the process flow, capture audit data, and pass values read from my configuration file to the child packages. You're working with a lot more tables at once than I am, but if you have time, the same idea might work for you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply