June 25, 2011 at 8:38 pm
I made changes to an Production DTS and need to import that dts file into prod system now with the same name. I tried to save it from the DEV system (where i made the modifications) using "Save As" and then choosing the Prod sql server. But it failed that the pkg already exists with different Id.
So, i saved it as a .dts file and tried to import into the prod. It also failed with the same error. How should I resolve this?
June 27, 2011 at 1:33 am
Have you tried dropping the existing package first?
-- Gianluca Sartori
July 5, 2011 at 6:51 pm
Deleting the existing packages is not at all an option. Because they have several versions of the package which they would revert back if there are any issues with the new version
July 6, 2011 at 2:19 am
I'm afraid there's not much you can do.
However, I would save previous versions of the package to a source control system, rather than relying on SQL Server versioning.
The only option I can think of is writing a VBScript to:
1) Load the source package from DEV (you get a handle to the latest version)
2) Load the destination package from PROD (you get a handle to the latest version)
3) Empty the destination package
4) Copy all objects from the source package to the destination package
5) Save the destination package
Somebody already tried this method:
http://us.generation-nt.com/answer/savetosqlserver-package-id-problems-help-53899742.html#r
What can I say?... Good luck!
-- Gianluca Sartori
August 26, 2011 at 2:36 pm
take a look at this routine:
http://www.nigelrivett.net/DTS/s_LoadPackageToServer.html
It worked for me... 🙂
September 8, 2011 at 5:33 am
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
-----------------------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply