Correct syntax for dtsrun

Somehow I'm not getting dtsrun to work.

The server is local

The name of the package is ImportHosttrncs. It is saved under Local Packages.

There is no password.

Is this syntax correct?

dtsrun /Slocalhost /PImportHosttrncs

I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts

-Jamie

Copying variables from another package

HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?

Thank you,

Ccote

There's no way to do this using the Designer. However a package is just an XML document so you try adding a new variable by processing those XML documents using XQuery.

If you work out how to do that - reply here and let me know

-Jamie

|||

HI Jamie, I have been able to achieve something interesting by using a script component with the test code below:

Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections("ChildPackage").AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
If Not package.Variables.Contains("VarTestNum") Then
package.Variables.Add("VarTestNum", False, "User", 0)
application.SaveToXml(packagename.ToString, package, Nothing)
End If

Dts.TaskResult = Dts.Results.Success

End Sub

The basis of the code I am using comes from Brian Knight web site.So far, I am not able to specify the type of the variable; It seems that SSIS resolve it using the Value method parameter (last one). I do not know if it will bw able to parse a date value correctly. But at least, if all I have to do is to change a type of a couple of variables in all package, the worst is done.

Ccote

|||

You're right about the variable type It is determined by the type of the object that you pass in that parameter.

-Jamie

Copying SSIS Packages

Is there any way to copy SSIS packages from one mirrored server to another? I'm considering using robocopy, but is there another solution? I'd then like to use this solution with the Transfer jobs task.Robocopy works for me in this scenario. Are you using a file-based or SQL Server deployment for the packages?|||I'm storing all packages in the msdb (package store) and we have our jobs pointing to the msdb. With SQL2000, we used the "transfer package" method described in www.sqldts.com, where we simply queried the table from our primary and copied those records to our warm-standby. With that solution on sqldts.com, is it possible to use the "sysdtspackages90" table to achieve the same results? I've also read that "dtutil" can be used to "copy" packages. In my case, I'm not sure on which method would be best though. Or, would it be better to store the packages in the file system?|||

DTUTIL is a good option if you are going with database storage. If you are using file storage, it isn't as valuable (unless you are also resetting GUIDs or passwords when you move them).

My personal preference is file system deployment. Others like database deployment. It really comes down to your preferences and specific requirements (though most requirements can be met be either).

copying server objects

I want to copy a database from one server to another. I'm happy about
how to do this but also want to copy a number of DTS packages, jobs and
alerts that relate to this database. Is there any way that I can copy
them or will I need to create them again on the new server.

Many Thanks

Laurence BreezeYou can copy DTS packages by opening up the current package and choosing
"Package/Save As..." from the menu bar. Be careful to make sure your
database references within the DTS package are still appropriate on the
new server.

Both jobs and alerts can be scripted and that script executed on the new
server - right click the jobs or alerts and choose "All Tasks/Generate
SQL Script...". More than one job or alert can be selected at a time if
you desire.

Good luck,
Tony Sebion

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk:

> I want to copy a database from one server to another. I'm happy about
> how to do this but also want to copy a number of DTS packages, jobs and
> alerts that relate to this database. Is there any way that I can copy
> them or will I need to create them again on the new server.
> Many Thanks
> Laurence Breeze|||Hi

You may want to read
http://support.microsoft.com/defaul...b;en-us;Q314546

John

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk...
>I want to copy a database from one server to another. I'm happy about how
>to do this but also want to copy a number of DTS packages, jobs and alerts
>that relate to this database. Is there any way that I can copy them or
>will I need to create them again on the new server.
> Many Thanks
> Laurence Breezesql

Copying DTS Packages to new server

Is there any way/an easy way to copy dts packages from one server to another? I cant seem to find a way to script them, is there any other way other than recreating them on the new server?
Regards JimPackages are saved in msdb..sysdtspackages - you just need to copy the relevant rows from that table.
Note that sysdtspackages is undocumented so this may not work in the future.

If you prefer this is an SP to load all packages and save them to files - just change the save to save to a server. This will lose the graphics as there is no context for it and no way at present to load the stream.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@.Path varchar(128)
as
/*

*/

set nocount on

declare @.objPackage int
declare @.PackageName varchar(128)
declare @.rc int
declare @.ServerName varchar(128)
declare @.FileName varchar(128)
declare @.FilePath varchar(128)
declare @.cmd varchar(2000)

select @.ServerName = @.@.ServerName ,
@.FilePath = @.Path

if right(@.Path,1) <> '\'
begin
select @.Path = @.Path + '\'
end

-- create output directory - will fail if already exists but ...
select @.cmd = 'mkdir ' + @.FilePath
exec master..xp_cmdshell @.cmd

create table #packages (PackageName varchar(128))
insert #packages
(PackageName)
select distinct name
from msdb..sysdtspackages

select @.PackageName = ''
while @.PackageName < (select max(PackageName) from #packages)
begin
select @.PackageName = min(PackageName) from #packages where PackageName > @.PackageName

select @.FileName = @.FilePath + @.PackageName + '.dts'

exec @.rc = sp_OACreate 'DTS.Package', @.objPackage output
if @.rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @.rc)
return
end

exec @.rc = sp_OAMethod @.objPackage, 'LoadFromSQLServer' , null,
@.ServerName = @.ServerName, @.Flags = 256, @.PackageName = @.PackageName
if @.rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

-- delete old file
select @.cmd = 'del ' + @.FileName
exec master..xp_cmdshell @.cmd, no_output

exec @.rc = sp_OAMethod @.objPackage, 'SaveToStorageFile', null, @.FileName
if @.rc <> 0
begin
raiserror('failed to save package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

exec @.rc = sp_OADestroy @.objPackage
end
go

Copying DTS Packages across Servers

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
TroyHi Troy,

You can save the packages as a structure file and save them to disk and then transfer them to your new server...

Or,.. you could get cunning and write a quick app to do it. I have some asp pages that I have written that will do this and I can flick them your way if you want... they will probably need tweaking to suit your needs...

What's the weather like in Auckland today??|||Hey thanks for that.
I will go and have a play and see what I can do - I assume it is a pretty straight forward process dumping them to file? And then reimporting them?

As for the ASP page - ummm errr, would love to take you up on that offer, but sadly I am about as literate with that sort of stuff as Cinderalla with a grenade laucher. (unless it is blatantly obvious). What are your thoughts on this?

Hmmm as for the weather - well all in all okay, and it is meant to be good through the weekend. Which will make a nice change because our new pool should have a chance to dry up and return to what was our lawn :(
Hopefully better in Aust.|||It's a relatively simple asp page,... the key thing is the SQL that we use. You could probably write it up as another stored proc or something to carry out the same function... ASP is a piece of cake to understand assuming some general knowledge of scripting languages...

Have a think about it and let me know.

Weather in Sydney today is overcast and cool,... not raining yet though... I think my cousin who is visiting from Chch brought this cloud with him...