SSIS newbie

[James Scho]

Hello,

I have been able to create SSIS packages for my Access 2003 table migration
to SQL 2005. After attempting to import the tables with Foreign key errors &
constraint errors I learned thru going thru some of these community threads
that you need to delete or disable the relationships & the Identity seeds
that I created in the SQL table PRIOR to any successful migration.

After thinking that I would only migrate once I figured, whate the heck - I
keep good notes & I can recreate them after the migration. Well, I did that
only to discover that I will need to migrate atleast 2 more times.

Can you assist me in perhaps working around this without deleting these
relationships & iIdentities - I believe I read something about making a
script to temporarily DISABLE these relationships, etc. MAKE the migration &
then ENABLE the relationships to be put in place again.

I hope this made sense - any input is appreciated.

thanks,
James

[Todd C]

Hello James and welcome to the SSIS(DTS) forums.

You're in luck. You can do everything you need right from within SSIS!

Since you are using SQL 2005, take these steps:
*Open your database in SQL Server Management Studio, and drill down the
nodes until you get to one of the tables in question.
*Expand the Keys node. Right-click on one of the keys and select "Script Key
as" >> "Drop and Create to" >> "New Query Editor window
*Open the SSIS Project/Package that loads this table.
*Create two Execute SQL Tasks on the Control Flow.
*Copy the "ALTER TABLE ... DROP CONSTRAINT ... " portion to one of the SQL
Tasks, and the "ADD CONSTRAINT" portion to another.

Now simply get them to execute in the correct order on either side of the
data flow component.

Note that you may need to run some of the ADD CONSTRAINT statements after
other tables have been loaded. A good practice would be to drop all the keys
first, then truncate all the tables, then load the data, then re-create the
keys.

You can also pack ALL the like statements into one task for simplicity.

HTH
=====
Todd C

Post by James Scho
Hello,
I have been able to create SSIS packages for my Access 2003 table migration
to SQL 2005. After attempting to import the tables with Foreign key errors &
constraint errors I learned thru going thru some of these community threads
that you need to delete or disable the relationships & the Identity seeds
that I created in the SQL table PRIOR to any successful migration.
After thinking that I would only migrate once I figured, whate the heck - I
keep good notes & I can recreate them after the migration. Well, I did that
only to discover that I will need to migrate atleast 2 more times.
Can you assist me in perhaps working around this without deleting these
relationships & iIdentities - I believe I read something about making a
script to temporarily DISABLE these relationships, etc. MAKE the migration &
then ENABLE the relationships to be put in place again.
I hope this made sense - any input is appreciated.
thanks,
James

[James Scho]

Thank you very much Todd - I will go ahead & try to implement - thanks for
your quick response!
best regards,
James

Post by Todd C
Hello James and welcome to the SSIS(DTS) forums.
You're in luck. You can do everything you need right from within SSIS!
*Open your database in SQL Server Management Studio, and drill down the
nodes until you get to one of the tables in question.
*Expand the Keys node. Right-click on one of the keys and select "Script Key
as" >> "Drop and Create to" >> "New Query Editor window
*Open the SSIS Project/Package that loads this table.
*Create two Execute SQL Tasks on the Control Flow.
*Copy the "ALTER TABLE ... DROP CONSTRAINT ... " portion to one of the SQL
Tasks, and the "ADD CONSTRAINT" portion to another.
Now simply get them to execute in the correct order on either side of the
data flow component.
Note that you may need to run some of the ADD CONSTRAINT statements after
other tables have been loaded. A good practice would be to drop all the keys
first, then truncate all the tables, then load the data, then re-create the
keys.
You can also pack ALL the like statements into one task for simplicity.
HTH
=====
Todd C

Post by James Scho
Hello,
I have been able to create SSIS packages for my Access 2003 table migration
to SQL 2005. After attempting to import the tables with Foreign key errors &
constraint errors I learned thru going thru some of these community threads
that you need to delete or disable the relationships & the Identity seeds
that I created in the SQL table PRIOR to any successful migration.
After thinking that I would only migrate once I figured, whate the heck - I
keep good notes & I can recreate them after the migration. Well, I did that
only to discover that I will need to migrate atleast 2 more times.
Can you assist me in perhaps working around this without deleting these
relationships & iIdentities - I believe I read something about making a
script to temporarily DISABLE these relationships, etc. MAKE the migration &
then ENABLE the relationships to be put in place again.
I hope this made sense - any input is appreciated.
thanks,
James