how to resolve Foreign Key constraint

  • I am completely new to SSIS and have been experiencing an impasse. I am trying to successfully execute a package containing

    17 tasks, I've resolved numerous errors, however, there is an error based on a FK constraint that I've not been able to resolve. Ive read a few articles on dropping and re-creating constraints but I feel they are complex, for a more advanced application, or are over my head from a familiarity standpoint. My question is, for a simple package and from an visual task aspect, how can I implement a SSIS feature in the specific task that will account for this error type.

    Below is a One Drive link to a screenshot of my solution

    https://1drv.ms/f/s!Ai9bJ2dV87SLgfwGrYuaLBK3qZgVjw

    _____________________________________________________________________
    As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi

  • The simplest way is normally to update the related (foreign key) tables, making certain that the values exists before attempting the insert / update of the other tables which have foreign key constraints.

    😎

  • Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?

    _____________________________________________________________________
    As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi

  • vega805 (7/27/2016)


    Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?

    First I have few questions;

    1) What is the data source?

    2) What is the destination db type (OLTP / DW)?

    3) Is SSIS running on an SQL Server Instance?

    Preferably one would use staging area/tables which greatly simplify the import process (ELT rather than ETL) by shifting the DB logic into the SQL Server rather than performing such operations within SSIS. SSIS is a great tool for pumping data and for process execution, it is less than mediocre for relational database logic.

    Slight side note, if your SSIS package has many tasks, think of simplifying it by separating those into several packages and execute those from a master package, much easier to maintain. My normal approach is to have a master package which creates and executes each individual task package on the fly reading the logic from configuration tables.

    😎

  • Thank you for the followup Eirikur.

    1) The data sources are separate OLTP databases on the same network, when I can successfully execute locally I will hand off the package to another network with different db credentials and will thus need to modify the connection manager.

    2) The destination db is also OLTP

    3) I don't believe the SSIS is running on a SQL Server instance, I'm not sure how I would validate that. Regardless, when I pass it off I believe it will run standalone. If it would help and it is NOT currently running on an instance I could change the strategy, I'm not sure how I would run against an instance.

    I wonder since the FK constraint issue is distinct to 1 of 17 tasks, that it may be best to manage within SSIS, I dont want to over-complicate this right now - there's a potential that the need/requirement will expand where I may need to run SSIS on a MSSQL instance and become more "industrial", but Ill have a lot more insight at that time.

    The second image in the below OneDrive directory illustrated the specific object Im having issue with.

    https://1drv.ms/f/s!Ai9bJ2dV87SLgfwGrYuaLBK3qZgVjw

    Separating tasks into several packages, though provoking... I can see the benefits. Once I get this to run I think I want to enhance the project by doing that.

    _____________________________________________________________________
    As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi

  • Since the constraint is due to TRUNCATE, you could remove the constraint while you run the package and then add it back.

    Alternative is to do an incremental load on your FK constraint table (Acct) instead of truncate and load. There is a stairway to SSIS explaining the incremental load http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/75331/%5B/url%5D

  • vega805 (7/27/2016)


    Ok but through SSIS right? Is this via adding an execution task? Where between the source and destination object within the task to you place such a execution task?

    Your package can contain multiple dataflows.

    Or you can have multiple packages, one for each dataflow, and run them one after another from a master control package.

    These multiple dataflows should run in logical order, such that the tables which are populated first are the ones whose values are the target of FK constraints.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply