Search results matching tags 'Oracle' and 'SSIS'

SSIS, preserving foreign key constraints.

Frederick — Thu, 24 Sep 2009 04:00:00 GMT

Hi, all.

For the past few days, I've been trying to use SSIS to migrate data in tables from a SQL Server database to an Oracle database. I need to retain foreign key constraints while doing it. I'm not in a position to remove foreign key constraints, then add them back.

The problem is that each of the foreign keys I'm using are newly generated GUIDs (created by SSIS).

Example:

I have two tables, "ShipClass" and "Ship". ShipClass contains a foreign Key pointing to Ship. That means the Ship table is loaded first, then ShipClass.
In the first dataflow, Ship is loaded appropriately in its' table - with a new Guid for its' foreign key field. ShipClass is loaded next, but the column containing the foreign key pointing to Ship always has a newly generated Guid.

The problem:
ShipClass has a FK relationship to Ship - both of their Guids absolutely have to be the same. I need a way to preserve foreign key constraints when I'm loading two different tables in SSIS.

I can't figure out how to do this. Can I get some help?

Moving data via SSIS.

Frederick — Thu, 10 Sep 2009 04:00:00 GMT

Hi, all.

This is a basic SSIS question.

IA co-worker of mine created several SSIS packages in Business Intelligence Development Studio. I have to use those packages to move data from an old database to a new one. (Creating new packages isn't an option.)

My problem:

A coworker created some SSIS packages. They the protection level "EncryptSensitiveWithUserKey." I changed the package protection level to "DontSaveSensitive", so those packages aren't encrypted specifically to her, but I get a new error message:

It says that a null password was given.

My question:

I have no idea where the password is supposed to be. If they have to be in a configuration file, can you tell me exactly where it is, with Syntax for including it?

Please note that I am transferring data from a SQL Server database to an Oracle database.

Re: Need to load a Sql Server temp table from a Oracle select results

mysorian — Thu, 16 Apr 2009 04:00:00 GMT

May be you should use SQL Server Integration Services. Set up a data flow task, get data from Oracle and push it to a SQL Server table. You may not even need to write code, but there may be some data conversions needed which can also be configured in SSIS.

If you are not proficient in the use of SSIS, I highly recommend my book which btw has even an Oracle <-->SQL Server example. This is a beginners book with very little code.

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

Fastload Tables into Oracle 10 through SQL Server 2005 SSIS

Charles Asbornsen — Thu, 30 Oct 2008 04:00:00 GMT

Hi. I know there is no easy way to do fastload through SSIS to an Oracle destination, so I have rearchitected the feedback portion of my ETL (99.9% from Oracle to SQL Server 2005, just a little feeding back to Oracle to allow server side filtering) so that there is just one place where it happens and it doesn't happen often. However, it still takes a good 20-30 minutes to load 3,048 rows here and that's ridiculous. In future releases I may not have the happy option of reducing the frequency of feedback needed for filtering and I might need to figure out a way to do bulk loading.

I know you can bulk load to Oracle through a text file but I don't know if it works in SSIS, and besides it would be really inelegant with scripts and strange configs and such, and possibly there would be security issues. Does anyone out there have a better solution to this problem? I understand that Microsoft wants to limit Oracle destinations for their ETLs to do a little market arm-twisting. Is there a custom destination control out there that can be loaded into the SSIS Toolkit?

Thanks.