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 |
|
man_mark
Starting Member
1 Post |
 Posted - 2004-07-17 : 10:34:47
|
Hi,I found this forum today, hopefully, I could get an answer here.  We receive multipal excel files from different companies. I need to import them into SQL.I have two DTS packages:1. I import an excel file into a staging table in SQL.2. When 1 is done, 2 will start to take the data in that staging table and insert into different tables.I all works fine, the problem is that in 2, sometimes, if one of the tables did not get inserted correctly, I would like it to roll everything back. So, in workflow properties in each step, I check "JOIN", "COMMIT", and "ROLLBACK" checkboxes, then I receive "....used by another transaction........", according to http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D279857I have to uncheck "use transaction" in Package Properties. My question is, how can I achive what I need in DTS? (Importing and Rollback)Thank you very much |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-18 : 21:28:35
|
| The import into the staging tables whiuld be considered a separate task. No need to roll this back as ir should be deleted on every run.For the move - the data is already in sql servver so do it in a stored proc (or a controlling sp calling aother SPs) and put the transaction control there. You can call the controlling SP from a dts package if you wish - or you may decide to do away with dts altogether and put the import from execl into the staging table in a stored proc instead.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
DTS importing/rollback question