Import SQL Server 2000 DTS package to new box
Data Transformation Services, DTS packages, Import DTS packages, SQL Server 2000, SQL Server 2000 import/export, SQL Server 2000 migration, Windows Server 2003
Hi,
I am building the new SQL 2000 SP3 server on Windows 2003 R2, in my old box there were alot of DTS package which is I need to import, my question is what account should I import it as to avoid the security right? in my old box I have 3 admin account only one own the DB whoever, DTS package were created by all 3 account.
Your advice is greately appreciated. Thank you.
Eddy
Software/Hardware used:
Software/Hardware used:
ASKED:
January 14, 2009 10:40 PM
UPDATED:
January 16, 2009 2:34 PM
RELATED QUESTIONS
- Executing DTSX from VB.NET 2003
- SQL Server 2000 DTS Designer components are required to edit DTS packages Error even after installing sqlserver2005_dts.msi
- Migrating from SQL Server 2000 to SQL Server 2005 on DTS packages
- Error copying indexes in DTS package
- Copying DTS packages from SQL Server 2000 to SQL Server 2005
Answer Wiki:
In SQL Server 2000, if you have a requirement to copy/transfer multiple DTS packages from one instance to the other, doing it manually becomes cumbersome. There are utilities in the market that do that for you but what if you have to make this import a part of your install? If you have such a requirement, then you can save your DTS packages as files and then write up a simple VB Script and call it through cscript to load up the packages. You can put this process into your install shield code to give a better end user experience.
/******************************************************************************/
‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>
‘
‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password
And here is the code for the ImportPackages.vbs (we use this in the case of slot_info in order to load the DTS packages):
option explicit
‘——————————————————————-
‘ ImportPackages.vbs — import DTS packages from .dts files.
‘
‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>
‘
‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password
‘
‘ This script imports all packages from the folder specified below to the
‘ specified SQL Server.
‘
‘——————————————————————-
const PACKAGE_FOLDER = “DTS Packages”
dim oPackage
dim oFile
dim sPath
dim iCount
dim sPackageName
dim sServer
dim sUserid
dim sPassword
dim oArgs
‘ Validate arguments.
set oArgs = wscript.Arguments
if oArgs.count < 3 then
wscript.echo “Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>”
wscript.quit 1
end if
sServer = oArgs(0)
sUserid = oArgs(1)
sPassword = oArgs(2)
‘ Obtain the path that the script is run in. The DTS package folder
‘ is relative to that path.
sPath = left(wscript.scriptfullname, len(wscript.scriptfullname) - len(wscript.scriptname))
dim fso
set fso = CreateObject(”Scripting.FileSystemObject”)
dim oFolder
‘ Make sure the package folder exists.
if not fso.FolderExists(sPath & PACKAGE_FOLDER) then
wscript.echo “Can’t find package folder ” & sPath & PACKAGE_FOLDER
wscript.quit 1
end if
set oFolder = fso.GetFolder(sPath & PACKAGE_FOLDER)
iCount = 0
‘ Iterate through the package files.
for each oFile in oFolder.Files
wscript.echo “Importing ” & oFile.Path
set oPackage = CreateObject(”DTS.Package2″)
‘ Get the package name, which is the name of the .dts file
‘ without the .dts extension.
sPackageName = left(oFile.Name, len(oFile.Name) - 4)
‘ Delete the package, but don’t error out if it already doesn’t exist.
on error resume next
oPackage.RemoveFromSQLServer sServer, sUserid, sPassword, , , , sPackageName
on error goto 0
‘ Load the package from the .dts file.
oPackage.LoadFromStorageFile oFile.Path, “”
‘ Save the package to SQL Server.
oPackage.SaveToSQLServer sServer, sUserid, sPassword
iCount = iCount + 1
‘ Release the package object. It can’t be reused.
set oPackage = Nothing
next
wscript.echo “Successfully imported ” & iCount & ” packages.”
wscript.quit 0
/******************************************************************************/
An excellent article on transferring the DTS packages in SQL 2000 is available at Darren Green’s site:
http://www.sqldts.com/204.aspx
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you for your greate input Karlg.
Eddy.