Changing owner of DTS packages

Question

Changing owner of DTS packages

Henrik Staun Poulsen

SSCertifiable

Points: 6584
More actions
September 16, 2003 at 9:44 am

#106227

One of my clients wishes to take ownership of the packages I've developed for them.
So I used Google, and found
http://www.databasejournal.com/features/mssql/article.php/1461511
Every time my client edits their packages, the owner get reset to my name, as described in http://www.sqldts.com/default.aspx?6,105,212,0,1
So what can I do?
1) Go on site, and use their PC to recreate the packages. Not pratical; too many packages.
2) Save the DTS as .VBS, and create a VB program to create the packages on site
or ?
I would love to hear a better option.
Regards,
Henrik Staun Poulsen
Stovi Software
Denmark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Quoted from http://www.sqldts.com/default.aspx?6,105,212,0,1

"They only way to permanently reflect a change of owner is to use the Save As function in the DTS designer. This creates a new package, not just a new version of an existing package."

Have you tried to save the package with new name, delete the old one and save the package with old name again?

philcart SSC-Forever Points: 47796 More actions · Answer 2

When you create a new packages on the server it uses the current user as the owner. As Allen_Cui pointed out, you can do this using Save As...'.

Given that you said your option 1 was not practical, you could save the packages at your site a Structured Storage Files. Thrn create a short VBScript that loads the package from the file and saves it to SQL Server. Then you just send the packages and the script to the client and they run the script.

Check out LoadFromStorageFile and SaveToSQLServer in BOL for the finer details.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Henrik Staun Poulsen SSCertifiable Points: 6584 More actions · Answer 3

Phill,

The idea of having a short VB Script sounds great.

Unfortunately, I've never written a VB Script before.

Would you mind pointing me in the right direction ?

Do you have any suggested reading or URLs on the subject?

If I cannot get the script to work, I'll use Allens suggestion.

Thank you for sharing the idea.

Regards

Henrik

philcart SSC-Forever Points: 47796 More actions · Answer 4

Here a script that I used to change the step name in about 120 packages. This needed to be done because the powers at be didn't like the wording



Const DTSSQLStgFlag_Default = 0 'Use SQL Server Authentication

Const DTSSQLStgFlag_UseTrustedConnection = 256 'Use Windows Authentication



Dim oPkg ' DTS Package object

Dim oStp ' DTS step object

Dim sPkgName ' DTS Package name

Dim iStps ' number of steps in collection

Dim iCntr ' counter for looping through steps collection



Dim sSvr ' server name

Dim sDb ' database name

Dim sUser ' user id

Dim sPwd ' password



Dim oConn ' ADO connection object

Dim oRS ' ADO recordset

Dim sConn ' ADO connection string

Dim sSQL ' SQL string to execute



' setup connection and recordset objects

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")

' assign values to connect to server

sSvr = "<your server>"

sDb = "<your database>"

sUser = "<your username>"

sPwd = "<your password>"

' build an ADO connection string

sConn = "Provider=SQLOLEDB.1Persist Security Info=True"

sConn = sConn & ";User ID=" & sUser

sConn = sConn & ";Password=" & sPwd

sConn = sConn & ";Data Source=" & sSvr

sConn = sConn & ";Initial Catalog=" & sDb

' build SQL Statement to retrieve list of packages

sSQL = "SELECT name FROM dbo.TmpPkgList"

' open ADO connection 

oConn.Open sConn

' open recordset

oRS.Open sSQL, oConn



While oRS.EOF = False

' assign package name in recordset to local variable

sPkgName = oRS.Fields(0).Value

' open a package object

Set oPkg = CreateObject("DTS.Package")

' load the package form the server

oPkg.LoadFromSQLServer sSvr, sUser, sPwd, DTSSQLStgFlag_Default, "", "", "", sPkgName

' get count of steps in package

iStps = oPkg.Steps.Count

' initialise loop counter

iCntr = 1



'loop through steps in package

While (iCntr <= iStps)

' set reference to step in error

Set oStp = oPkg.Steps(iCntr)

If Instr(1, oStp.Description, "Error Log", vbTextCompare) > 0 Then

' use the Replace function to change the activescript text to the new number

oStp.Decription = Replace(oStp.Description, "Error Log", "Error Reporting", vbTextCompare)

End if

' increment counter

iCntr = iCntr + 1

' close step object

Set oStp = Nothing

Wend

' save changed package back to server

oPkg.SaveToSQLServer sSvr, sUser, sPwd, DTSSQLStgFlag_Default

' close package object

Set oPkg = Nothing

' move to next record

oRS.MoveNext



Wend

' close and release ADO objects

Set oRS = Nothing

Set oConn = Nothing

Basically what you'll need to do is,

Use a filesystemobject to pick your package names from the saved files.

Check for the existence of the package first.

If it does exits, use RemoveFromSQLServer to delete it.

Load the package from the file using LoadFromStorageFile

Then save it to the server using SaveToSQLServer.

Check out www.sqldts.com. They have a vast wealth of information. Specifically, take a look at the looping mechanism they used in this article http://www.sqldts.com/default.aspx?246. You'll need to do something like this to load each of your packages in turn.