August 12, 2003 at 11:46 am
Is it possible to script a DTS package so that it can be recreated on another server? I know you can output a DTS package to a structured file or to VB (6.0?) code, but I want to script the DTS package in the same way that a table or stored procedure is scripted. The purpose is to be able to run a script created on a test server on a production server and to also use this for disaster recovery. I have searched high and low and cannot find any way to do this.
August 12, 2003 at 12:31 pm
There is a utility at http://www.sqldts.com/default.aspx?6,105,204,0,1 called DTSBackup 2000.
It can help you move the package, but I am not sure you'll be able to script it something like CREATE DTS... Its not stored that way.
And as far as I know, its not stored in the MDF. If you move an MDF from server-A to server-B, you will not have your DTS packagage(s) unless you explicitly move them as well.
That site provides some methods of moving a package.
August 13, 2003 at 6:46 am
Since DTS packages are stored in the MSDDB database as records in the SYSDTSPACKAGES table, you can select the appropriate records (package) and insert it into a different server and database. Creating a DTS package to perform the copy will allow you to automate this. Simply put this query in a transformation task between your two servers. Here is the query...
SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN ( SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM dbo.sysdtspackages
where
[name] = 'My_DTS_Package'
or [name] = 'My_Other_DTS_Package'
GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
One thing I have found as a problem with this is the ownership of the package can get messed up, but maybe this won't be a problem for what you are doing. I take no responsibility for any loss of data or incorrect results.
August 13, 2003 at 6:46 am
The following script does the export. You can write on the same lines to import into other server.
'-- Srikanth Goli
'-- 8/5/03
'-- Export or Import DTS packages from/into SQL Server in *.dts format
'-- Check whether the directory name and direction of tranfer passed as a parameter
On Error Resume Next
Set objArgs = Wscript.Arguments
If objArgs.Count<> 3 then
Wscript.Echo "Invalid argument numbers. Provide directory of *.dts files, Servername and direction of transfer."
Wscript.Echo "Usage : ExportDTS.vbs C:\DTS ServerName OUT"
Wscript.Quit
End If
Set objDataConn1 = wscript.CreateObject("ADODB.connection")
datasourcestr = "Data Source=" & objArgs(1) & ";"
wscript.echo datastr
objDataConn1.Open "Provider=sqloledb;" & _
datasourcestr & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
SQL = "select distinct name from msdb..sysdtspackages "
wscript.echo SQL
Set rs = wscript.CreateObject("ADODB.RecordSet")
RS.Open sql, objDataConn1
Do While Not RS.EOF
set objDTS1 = wscript.CreateObject("DTS.Package")
wscript.echo "Exporting " & rs(0).value
objDTS1.LoadFromSQLServer objArgs(1),,,256,,,,rs(0)
savetofile = objArgs(0) & "\" & rs(0).value & ".DTS"
wscript.echo "Saving to file " & savetofile
objDTS1.SaveToStorageFile savetofile
objDTS1.Close
RS.Movenext
Loop
objDataConn1.Disconnect
objDataConn1.Close
August 13, 2003 at 6:58 am
quote:
The following script does the export. You can write on the same lines to import into other server.
Where do you run this script from? A VB application?
August 13, 2003 at 8:43 am
Lee,
I tried your query, but got no records returned. Even running just the first line of SELECT T1.* FROM dbo.sysdtspackages got NO records. I'm on the MSDB of a server where there are plenty of DTS packages. Any ideas on why the query doesn't seem to be working?
Thanks.. Mark
quote:
Since DTS packages are stored in the MSDDB database as records in the SYSDTSPACKAGES table, you can select the appropriate records (package) and insert it into a different server and database. Creating a DTS package to perform the copy will allow you to automate this. Simply put this query in a transformation task between your two servers. Here is the query...SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN ( SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM dbo.sysdtspackages
where
[name] = 'My_DTS_Package'
or [name] = 'My_Other_DTS_Package'
GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
One thing I have found as a problem with this is the ownership of the package can get messed up, but maybe this won't be a problem for what you are doing. I take no responsibility for any loss of data or incorrect results.
August 13, 2003 at 8:49 am
quote:
Lee,I tried your query, but got no records returned. Even running just the first line of SELECT T1.* FROM dbo.sysdtspackages got NO records. I'm on the MSDB of a server where there are plenty of DTS packages. Any ideas on why the query doesn't seem to be working?
Thanks.. Mark
quote:
Since DTS packages are stored in the MSDDB database as records in the SYSDTSPACKAGES table, you can select the appropriate records (package) and insert it into a different server and database. Creating a DTS package to perform the copy will allow you to automate this. Simply put this query in a transformation task between your two servers. Here is the query...SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN ( SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM dbo.sysdtspackages
where
[name] = 'My_DTS_Package'
or [name] = 'My_Other_DTS_Package'
GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]
One thing I have found as a problem with this is the ownership of the package can get messed up, but maybe this won't be a problem for what you are doing. I take no responsibility for any loss of data or incorrect results.
The query worked for me. My final solution combined the suggestions of several people. In a batch file, I copy the latest version of the DTS packages to a file that is stored in sql binary format (which I found to be about 50% smaller than an ASCII file) using this command:
bcp.exe "SELECT T1.* FROM msdb.dbo.sysdtspackages AS T1 INNER JOIN (SELECT [
name], [id], MAX([createdate]) AS [createdate] FROM msdb.dbo.sysdtspackages GROU
P BY [name], [id]) AS T2 ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createda
te]" queryout "c:\download\DTSPackages.bin" -n -SCHIDEV01 -T
CHIDEV01 is the server where the packages were created and is considered the source server.
Then I use bcp again to import the file into the destination server:
bcp.exe msdb.dbo.sysdtspackages in "c:\download\DTSPackages.bin" -n -SJOHND -T
This is what I was after all along. The ability to incorporate the DTS package transfer into a larger script that can be run by production control without manual intervention. Thanks to all for your contributions!
August 14, 2003 at 4:39 pm
Drewj840,
The script you asked about can be pasted into a file with a .vbs extension and run using the scripting engines on your computer by double-clicking the filename.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy