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