January 15, 2009 at 6:05 am
I was asked to set up an ETL procedure which transfers data from an extremely flexible Oracle source to SQL Server: metadata is extracted from Oracle tables at runtime, implying that target table and column names are not known prior to execution.
taking advantage of custom variables in SSIS, I developed a package which succeeds in extracting the required metadata from the source tables and in creating the desired target tables in SQL Server (mainly using script components). however, SSIS does not seem to allow data flow to tables/columns with dynamic names: as soon as the name differs from the name used during setup of the package, SSIS stops execution ! from what I can find in various forums, prior knowledge of target table/column names turns out to be essential for any SSIS package.
I was then told to use the import/export wizard for this project, which is what I'm currently looking into: the data will be prepared by a program on the application side, and placed into a dedicated schema READY FOR PICKUP. it seems to me, however, that the import/export utility also requires that all table names are known prior to execution.
hence the following question to you experts out there: can SSIS import an entire Oracle scheme, without knowing the names of the tables, nor the number of tables to be transferred ?
many thanks in advance for any replies whatsoever !
January 15, 2009 at 6:18 am
Select TblName from Oracle.DbName.Owner.SystemTables (assuming they exist).
Loop in that list and execute this :
Select * into LocalDb.dbo.? FROM Oracle.DbName.Owner.?
That however doesn't transfer any constraint, indexes, identity properties.
January 15, 2009 at 6:58 am
Once an SSIS data flow is created, it does not like schema changes. This is because the inputs and outputs of each of the sources, transformations, and destinations contain the meta-data.
Now, that being said, MS has examples and even sample code on the MSDN website for generating meta-data driven ETL processes that dynamically build the SSIS packages and the data flows within them. It's actually not that difficult, but it does require some knowledge of a .Net language and some programming.
January 20, 2009 at 1:10 pm
thanks for your replies ! I will only have the opportunity to try it out next week, though.
thanks also for the tip about the MSDN site ... how could I forget ?
January 21, 2009 at 5:16 am
Don't worry about it, I recentrly to lookup for somethign in bol and asked a question in here!!!
Talk about irony :D:w00t:
March 19, 2009 at 7:52 am
hi Ninja's_RGR'us,
I finally got round to trying out the script you suggested, but without success ! obtaining the list with table names in no problem at all, it's the next part that's causing troubles.
you suggested I execute the following: Select * into LocalDb.dbo.? FROM Oracle.DbName.Owner.?
this needs to be done in a Execute SQL task, right ?
for that task, I need to specify the connection: this needs to be the one pointing to the oracle db, right ?
now when the following is executed
Select * into LocalDb.MY_DB.? FROM Oracle.MY_DB.MY_SCHEME.?
I obtain the error "ORA-01747: invalid user.table.column, table.column, or column specification"
is the above syntax correct ?
I will now try and generate the sql in script, so that it can be passed in a variable and potential parameter mapping problems will be eliminated.
March 19, 2009 at 9:38 am
hello again,
still no luck, even with SQL passed on as variable.
are you sure it is possible to transfer tables from oracle to sql server ? in that case I'm assuming it cannot be accomplished by a simple Execute SQL task ?
thanks again
March 19, 2009 at 5:55 pm
I would just start small.
First make sure you can do a select on the oracle box from the sql server box (will required a linked server or openrowset / openquery).
Then make the select into.
Then learn to list the tables on the oracle box
Open a cursor and loop and build dynamic sql to run this.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply