Import SQL Server 2000 DTS package to new box

55 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bjet
    Thank you for your greate input Karlg. Eddy.
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following