Welcome to EMC Consulting Blogs

I'm building a SSIS project that gets reloaded from a source system nightly. In order to perform a TRUNCATE TABLE on the primary key table, you've got to DROP (not just disable) the foreign key constraints. I did this in a script transform that produces DropFkScript and CreateFkScript variables, which are then executed before and after loading, respectively.

Hi, Jamie. Thanks for this post. However, I'm a little confused about what problem we're trying to solve.

Is the issue that the first table has an identity-based PK that needs to be known for the subsequent insert into the second table? If so, then wouldn't an OLE-DB Command transformation with an output parameter mapped to an existing field on the row do the trick? In other words:

- add placeholder int field to row using Derived Column

- use OLE-DB Command transform to call an insert stored procedure that returns the new identity in an output parameter; map the output parameter to the int field created in the previous step

- use a subsequent OLE-DB Command transfor to insert into the second table, mapping the newly initialized int field to the FK column in the table

What do you think? Or am I discussing a different topic altogether?

Dan

Good post,

It would also be helpful to add a SQL query to find out which FK's you need to disable,

Thanks

Hi, ver useful blog, but i did'nt get this one right. where do i have to insert that script (ALTER TABLE <table_name> NOCHECK CONSTRAINT <constraint_name>), isn't DDL tasks supposed to be used on Analysis services.

Hi, thanks for the quick answer.

I know this post isn't about analysis services, but you mentioned about using a DDL to disable constraints before doing the insert. right? well the only place in SSIS I found something about DDL is in the toolbar in control flow, and that's an analysis services task.

I hope it's more clear now.

If the source tables have the relationships defined, you could do this:

1.  Use the proc defined here:  http://www.databasejournal.com/img/usr_sp_drop_constraints.sql  to drop all constraints on just the problem tables. (found via trial and exception)

2.  Then use the Copy SQL Objects to copy the tables (after step one is done).  If you include the option to copy foreign keys then, it will recreate the ones found on the source tables.

Maybe a dumb question: doesn't the OLEDB destination Fast Load / Check Constraints option handle this situation? E.G. If I make a single data flow with two destinations, and the destinations are set for OLEDB with Fast Load, there's an option for Check Constraints that can be switched off, and it seems like the resulting behavior is the same as if you'd disabled constraints and then enabled them again.

Ah, I think I just answered my own question: if the load into the FK constrained table commits first, before all the data in the parent table has finished loading, it'll fail. Nevermind :-).

It's really a shame that there's no way to make a precedence constraint, inside a single data flow, requiring one destination load to complete before another. It seems like that'd also solve this issue.

I did manage to create another method (I'm sure someone else has done this too, so I'm not claiming to have invented this :-). This I find to be a little cleaner/more in the "spirit" of the data flow notion, but be warned that I'm fairly sure it would be too slow for a large quantity of data where performance is important. The integration I am working on involves collecting invoices in files from an FTP site, staging them locally and then pushing the staged data into various databases. Each has a header that is numbered using Identity in the target database, and a related table of detail rows. I found that the SSIS destination components won't allow precedence constraints as noted above, but the OLEDB Command component will, and will pass rows "though" to subsequent transformations. That means I can make a stored proc in the target database (with an output parameter to collect new row IDs) for inserting a header row, and a second for inserting a detail. In the data flow, I set up the data source as a join between incoming headers and details so I get a row for every detail, but each one contains the header data as well. I then multicast this into two pipelines, one for headers and one for details. In the header pipeline a Sort with Remove Duplicates filters the rows to unique header data. The resulting headers are then passed through an OLEDB command component to insert each one using the stored proc, and fetch the new ID from the proc's output parameter. The output of that component is then merge-joined back with the original detail rows from the multicast. The join results can then be put through a second OLE DB command component to insert the detail rows including the parent row IDs. That's basically it ( though my real process is "decorated" with some other stuff not relevant to this question. So, if you face this issue with a modest quantity of data, I think this works, and it's all one flow/one transaction, with no temp files, and the FKs remain in place in your target DB.

In the past I have used Method 2 (Drop/Add FK with check) when importing a large amount of data.  750MB worth of data that gets cleaned resulting in 120k Parent Rows with 300k Child rows.  I used a custom script component to split into two outputs (Parent and Child) going to an SQL Destination.  This loaded very fast for the amount of data involved/network traffic.  Thanks goes out to Jamie for this idea.

I have also used XML to handle the parent -child problem.  I generate a unique ID for each Parent using a custom script component.  Then, use a custom script component to form the XML and output each row uniquely with the attached XML.  Then, use a stored procedure that takes as an input the Parent Data as columns and the child data as an XML column.  (The stored procedure performs the business logic, and handles conversion of XML to the child table).

Why use the XML method?  Entity Attribute Value (EAV) scenarios.  For instance, you have to accommodate multiple import files where the number of columns keep changing.  So perform an UNPIVOT, convert to XML, and import using a stored procedure.  What rocks about this is that when it's time to do a new import you copy an existing one, update your UNPIVOT to include the new columns, and done.

PingBack from http://sydnee.onlinepokervidsdirect.info/ssischecktableforrow.html