Calling a DTS (SSIS 2005) from a Vbscript

Hello,

I would like to run a DTS using a Vbscript, i managed to get the DTS running, but i would like to pass the value of a global variable, but im having lots of dificulties.

Since it is a VBS i only have access to the things registered on the registry of the machine, so i don't have access to the microsoft.sqlserver.dts.runtime, at is maximum.

So i do the following

Set objApplication = CreateObject("MSDTS.Application")

Set objDTSPackage = objApplication.LoadPackage("T.dtsx", True, Nothing)

'This should pass the variable but insted i have an error

set pkgVars = objDTSPackage.Variables

set myVar = objDTSPackage.Variables.Add("User::Name", false, "User", "TesteA")

'End of the error part

DTSResults = objDTSPackage.Execute()

The error that i got is the following

"Object doesn't support object or method"

What am i doing wrong?

Thank you

Nuno Ferreira

this link might help: http://msdn2.microsoft.com/en-us/library/ms136090.aspx|||

This link refers the use of microsoft.sqlserver.dts.runtime.

What happens is that since im using Vbscript, i do not have access to microsoft.sqlserver.dts.runtime. Is there a way to create the object from microsoft.sqlserver.dts.runtime in the script?

If not how can i in the script assign a variable value to the DTS

|||

hi,

It was very useful have any link with that information. I am pursuing for any sort of stuff like that too.

regards,

|||

Nfrf wrote:

This link refers the use of microsoft.sqlserver.dts.runtime.

What happens is that since im using Vbscript, i do not have access to microsoft.sqlserver.dts.runtime.

the ssis assemblies are managed wrappers for com objects.

Is there a way to create the object from microsoft.sqlserver.dts.runtime in the script?

yes. you need to figure out the progid for the object that you wish to create. this can be accomplished by looking in the registry.

|||

I have run SSIS packages in a Batch file using a Scheduled Task and it's much less complicated than trying to use VB, here is an example:

dtexec.exe /file F:\SSIS_Packages\MyPackage1.dtsx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
dtexec.exe /file F:\SSIS_Packages\MyPackage2.dtsx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
dtexec.exe /file F:\SSIS_Packages\MyPackage3.dtsx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
dtexec.exe /file F:\SSIS_Packages\MyPackage4.dtsx /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

Optional Switches:

Options are case-insensitive.
A hyphen (-) may be used in place of a forward slash (/).
/CheckF[ile] [Filespec]
/Checkp[ointing] [{On | Off}] (On is the default)
/Com[mandFile] Filespec
/Conf[igFile] Filespec
/Conn[ection] IDOrName;ConnectionString
/Cons[oleLog] [[DispOpts];[{E | I};List]]
DispOpts = any one or more of N, C, O, S, G, X, M, or T.
List = {EventName | SrcName | SrcGuid}[;List]
/De[crypt] Password
/DT[ S] PackagePath
/F[ile] Filespec
/H[elp] [Option]
/L[ogger] ClassIDOrProgID;ConfigString
/M[axConcurrent] ConcurrentExecutables
/P[assword] Password
/Rem[ark] [Text]
/Rep[orting] Level[;EventGUIDOrName[;EventGUIDOrName[...]]
Level = N or V or any one or more of E, W, I, C, D, or P.
/Res[tart] [{Deny | Force | IfPossible}] (Force is the default)
/Set PropertyPath;Value
/Ser[ver] ServerInstance
/SQ[L] PackagePath
/Su[m]
/U[ser] User name
/Va[lidate]
/VerifyB[uild] Major[;Minor[;Build]]
/VerifyP[ackageid] PackageID
/VerifyS[igned]
/VerifyV[ersionid] VersionID
/W[arnAsError]

|||

Thanks this could be of help.

One other thing. And if you like to pass a variable in the command line how do you do it?

Thanks