DTS.SaveToSqlServer

[Leo Matter]

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.aspx?scid=kb;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

[Leo Matter]

Post by Leo Matter
Hi
I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....
http://support.microsoft.com/default.aspx?scid=kb;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.
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

[frank chang]

Leo, Having experienced this phenomenon myself, I referenced this link
http://groups-beta.google.com/group/microsoft.public.sqlserver.dts/browse_frm/thread/7de6488e4f25496e/40c0b32dd4419e10?q=DTS+Diagram+Changes+group:microsoft.public.sqlserver.dts&rnum=5&hl=en#40c0b32dd4419e10

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.

Post by Leo Matter
Hi
I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....
http://support.microsoft.com/default.aspx?scid=kb;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.
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

[Leo Matter]

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

Post by frank chang
Leo, Having experienced this phenomenon myself, I referenced this link
http://groups-beta.google.com/group/microsoft.public.sqlserver.dts/browse_frm/thread/7de6488e4f25496e/40c0b32dd4419e10?q=DTS+Diagram+Changes+group:microsoft.public.sqlserver.dts&rnum=5&hl=en#40c0b32dd4419e10
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.

Post by Leo Matter
Hi
I am trying to copy DTS packages between servers using vbscript, and
absolutely want to keep the text annotations, design, etc....
http://support.microsoft.com/default.aspx?scid=kb;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.
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

[Pavan Pandurang]

Hi Leo

Would greatly appreciate this workaround. Can you please email me this?
***@hotmail.com

url:http://www.ureader.com/msg/11461088.aspx

[vinod agole]

hello Pavan,

Did you got the solution, can you please give it to me if you have it.

url:http://www.ureader.com/msg/11461088.aspx

[vinod agole]

hi leo,

can you please forward me a copy of the solution on ***@gmail.com.

url:http://www.ureader.com/msg/11461088.aspx