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 |
|
Aarion
Starting Member
2 Posts |
 Posted - 2011-04-20 : 06:39:34
|
| Hi .. I am new to DTS packages. I would be grateful if you could provide me with the best way to do a task. I need to copy db tables from one MSSQL DB to another MSSQL DB. Eg: From DB1.TableA needs to be copied to DB2.TableA every night. DB2.TableA needs to be over written every night as the table columns could change. I have done this by creating an OLEDB connection and an Execute-SQL Task.But I also need to have a Send-mail task to notfy of any failures in the process. Also, the DTS package will have to copy across a number of such tables from DB1 to DB2.If you could let me know of the best way to do this. Many thanks .. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-20 : 08:41:36
|
| Haven't used dts for a long time but:You have separate tasks here.Creating the packageSending the emailScheduling the job.The scheduling will be done from the sql server agaent. I would create two steps - one to do the transfer followed by another to send the email.You can create the job from the package by scheduling it then adding the email step or by creating the job then adding both steps (I would do the latter).I would also add a dummy step as the first step so that you can see from the history that it is running.For the package as it doesn't sound like you know much about dts I would right click on the database and use the import/export wizard.After you have selected the tables it gives an option to save the package. After you have done this you can amend it as you wish.Note - I would hold the package on disk rather than in masdb and call it via a dtsrun command.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Aarion
Starting Member
2 Posts |
Posted - 2011-04-21 : 02:27:24
|
| Nigel - Many thanks for the reply. I will try and find the option of sending the email from the job as you suggested.Many thanks for your suggestions. |
|
|
|
|
|
|
DTS Package Query