DTS.SaveToSqlServer

#1

Hi

I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....

as documented here, this does not work:
http://support.microsoft.com/default...;EN-US;Q249901

and I do not understand the workaround.

it should possible to do it, since with Enterprise Manager you can do it,
since service pack 2 or 3. save to storage file, reopen it, and still looks
wonderful.

here is my code so far:

Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim CurrentFolder: Set CurrentFolder = fso.GetFolder(".")

Dim Files: Set Files = CurrentFolder.Files
Dim File
For Each File In Files
if UCase(right(File.Name, 3)) = "DTS" then
dim oPkg
set oPkg = createobject("dts.package")
opkg.LoadFromStorageFile file.path, ""
'msgbox(opkg.Name)
'I rename the package intentionally to the file name - with SaveToSqlServer
it does not work either
opkg.SaveToSQLServerAs file.name, servername,sa_user,
sa_pw,DTSSQLStgFlag_Default,"","",,nothing,0
end if
Next

does anybody have a solution?
i probably could also do it with BCP and sp_add_dtspackage - but it is not
documented so probably should not do it.

Kind Regards
Leo Matter

#2

"Leo Matter" <leo (AT) remove_this (DOT) cis-olap.com> wrote

Quote:

Hi

I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....

as documented here, this does not work:
http://support.microsoft.com/default...;EN-US;Q249901

and I do not understand the workaround.

it should possible to do it, since with Enterprise Manager you can do it,
since service pack 2 or 3. save to storage file, reopen it, and still
looks wonderful.

here is my code so far:

Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim CurrentFolder: Set CurrentFolder = fso.GetFolder(".")

Dim Files: Set Files = CurrentFolder.Files
Dim File
For Each File In Files
if UCase(right(File.Name, 3)) = "DTS" then
dim oPkg
set oPkg = createobject("dts.package")
opkg.LoadFromStorageFile file.path, ""
'msgbox(opkg.Name)
'I rename the package intentionally to the file name - with
SaveToSqlServer it does not work either
opkg.SaveToSQLServerAs file.name, servername,sa_user,
sa_pw,DTSSQLStgFlag_Default,"","",,nothing,0
end if
Next

does anybody have a solution?
i probably could also do it with BCP and sp_add_dtspackage - but it is not
documented so probably should not do it.

Kind Regards
Leo Matter

#3

Leo, Having experienced this phenomenon myself, I referenced this link
http://groups-beta.google.com/group/...b32dd44 19e10

In this link, Darren Green recommends that you use an alternate method to
copy
packages between SQL Servers. I'm hoping this problem gets solved in SQL
Server
2005 Integration Services. There is a release candidate for SQL Server 2005
rumored to be available in the July to September 2005 time frame. You could
test the current release candidate for SQL Server 2005, convert your VB
package to VB.NET, and then try using the VB.NET equivalent to the SaveToSQL
Server method . If it doesn't work, I guess you could contact Microsoft.

"Leo Matter" wrote:

Quote:

Hi

I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....

as documented here, this does not work:
http://support.microsoft.com/default...;EN-US;Q249901

and I do not understand the workaround.

it should possible to do it, since with Enterprise Manager you can do it,
since service pack 2 or 3. save to storage file, reopen it, and still looks
wonderful.

here is my code so far:

Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim CurrentFolder: Set CurrentFolder = fso.GetFolder(".")

Dim Files: Set Files = CurrentFolder.Files
Dim File
For Each File In Files
if UCase(right(File.Name, 3)) = "DTS" then
dim oPkg
set oPkg = createobject("dts.package")
opkg.LoadFromStorageFile file.path, ""
'msgbox(opkg.Name)
'I rename the package intentionally to the file name - with SaveToSqlServer
it does not work either
opkg.SaveToSQLServerAs file.name, servername,sa_user,
sa_pw,DTSSQLStgFlag_Default,"","",,nothing,0
end if
Next

does anybody have a solution?
i probably could also do it with BCP and sp_add_dtspackage - but it is not
documented so probably should not do it.

Kind Regards
Leo Matter

#4

Thanks Frank for your response
in the meantime I found a hack using directly the data in sysdtspackages,
which is in fact a structured storage file stored in the database. i made a
small vb.net command-line utility, which can save packages to the filesystem
and load them again using sp_add_dtspackages. it preserves the layout, makes
valid .dts storage file but i cannot have the same package twice on the same
server because i am unable to change the packageID.
however, this functionallity fits my most urgent needs. if someone wants a
copy e-mail me.
regards
Leo Matter

"frank chang" <frankchang (AT) discussions (DOT) microsoft.com> wrote

Quote:

Leo, Having experienced this phenomenon myself, I referenced this link
http://groups-beta.google.com/group/...b32dd44 19e10

In this link, Darren Green recommends that you use an alternate method to
copy
packages between SQL Servers. I'm hoping this problem gets solved in SQL
Server
2005 Integration Services. There is a release candidate for SQL Server
2005
rumored to be available in the July to September 2005 time frame. You
could
test the current release candidate for SQL Server 2005, convert your VB
package to VB.NET, and then try using the VB.NET equivalent to the
SaveToSQL
Server method . If it doesn't work, I guess you could contact Microsoft.

"Leo Matter" wrote:

Hi

I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....

as documented here, this does not work:
http://support.microsoft.com/default...;EN-US;Q249901

and I do not understand the workaround.

it should possible to do it, since with Enterprise Manager you can do it,
since service pack 2 or 3. save to storage file, reopen it, and still
looks
wonderful.

here is my code so far:

Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim CurrentFolder: Set CurrentFolder = fso.GetFolder(".")

Dim Files: Set Files = CurrentFolder.Files
Dim File
For Each File In Files
if UCase(right(File.Name, 3)) = "DTS" then
dim oPkg
set oPkg = createobject("dts.package")
opkg.LoadFromStorageFile file.path, ""
'msgbox(opkg.Name)
'I rename the package intentionally to the file name - with
SaveToSqlServer
it does not work either
opkg.SaveToSQLServerAs file.name, servername,sa_user,
sa_pw,DTSSQLStgFlag_Default,"","",,nothing,0
end if
Next

does anybody have a solution?
i probably could also do it with BCP and sp_add_dtspackage - but it is
not
documented so probably should not do it.

Kind Regards
Leo Matter

#5