Post reply

Discovering Table Order Precedence

  • March 26, 2011 at 10:28 pm

    #145326

    I have a development project where we are building a new database and deploying it empty, from a Visual Studio 2010 project. Now I need to develop a SSIS package to load the tables from production using SSIS Data Flow Tasks. We are cleaning the data in a previous step, but the empty tables do have the Foreign Key constraints enabled. This means that I have to load the tables in parental precedence order or the FK check constraints will bounce the ETL load.

    I am going to setup Data Flow Tasks for each independent table, so that I can get parallel loads. Those tables that are dependent on PK-FK constraints will comprise their own data flow task. The manual way to do this is to setup each data flow task, try it and see what FK constraint complains, and then move the data flow task up in the precedence order to the parent table.

    I have over 500 tables that I need to do this with. Is there a tool, or strategy that will allow me to discover the proper order of loading the tables so that I can eliminate the trial-and-error method of determining the table order of precedence?

    Thanks!

    Brandon_Forest@sbcglobal.net

  • March 27, 2011 at 5:55 am

    #1304045

    yes, there is a built in stored procedure that will give the hierarchy of the schema based on foreign keys and dependencies.

    this is what i typically use:

    EXEC sp_msdependencies @intrans = 1

    you can stick the results in a table, and then filter for objecttype 8(tables)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 27, 2011 at 3:20 pm

    #1304130

    Just what I needed. Thanks! 😀

Viewing 3 posts - 1 through 2 (of 2 total)

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