Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NW
Starting Member
2 Posts |
 Posted - 2004-05-23 : 11:19:11
|
| I'm having a dickens of a time exporting an Excel file with numerous columns that go to several destination tables in SQL Server. Using the DTS Designer, I can map the columns in spreadsheet to the fields in the corresponding destination tables, but when a destination table has a foreign key that value is not being updated. In other words, the primary (identity key) in the parent table is automatically incremented, but this value remains NULL in the child table. For example, I tested a simple package to import two columns from excel into a Class table (parent) and ClassDate table (child). The import "works" for the Class table, but the foreign key (ClassKey) in the ClassDate child table gets a NULL (not good for future joins). I toggled the "Identity Insert" check box without success. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-23 : 19:56:10
|
| The problem, I suspect, is that DTS doesn't recognise that the ClassDate is a child of Class, so won't automatically update with the required key. You can specify the primary key in your spreadsheet (that's what IDENTITY INSERT does), but that can cause other problems. A way around this is to use an ActiveX script to populate the data. You have more control with these, and can do things such as read the recently inserted primary key so you can use this to populate your child tables. HTH, Tim |
 |
|
|
NW
Starting Member
2 Posts |
Posted - 2004-05-24 : 09:15:01
|
| Thanks! Now I'll have to see if I can dig up an ActiveX script. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-30 : 19:40:52
|
| Check out BOL - there's a few in there you can have a look at to get you started.Tim |
|
|
|
|
|
|
|
|
Keep key constraints during csv import