SSIS : How to copy data from SQL Server to Oracle

[ton12]

I have a table TAB1 in SQL Server and would like to copy
its content to a table TAB1 on ORACLE having the
same column names and datatypes.

The database connection to the Oracle database has
been set and I can see the table.

Any suggestions how to implement it in task in SSIS?

Thanks.
Ton

 

[mrdenny]

Drop a data flow task into the package, then define source and destination objects.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ton12]

Thanks fo the data flow task solution but I need
to cater for different table names.

I might have to copy Tab2 or Tab11 to Oracle
but I w'ont know in advance the table name that
needs to be copied. The matching table name will
exist in Oracle.

The value of the table name to be copied is determined
in previous tasks and is saved in a package variable.

Any suggestions.

Thanks in advance
Ton

 

[mrdenny]

I haven't found an easy way to change the objects within a data flow task. I had to do this once from a SQL Server to another SQL Server so I setup objects which would BCP the data out then BCP the data back in.

You could probably use the same technique BCP the data out to a CSV file then use the native Oracle command line importers to load the CSV file in.

You can probably programmatically setup a data flow task in memory in a .NET script which would allow you to configure it all via code. This would require quite a bit more work, but would probably be more stable in the long run.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog