Convert VbScript Com object to PS to load sql2005 DTS pkg

Question

Convert VbScript Com object to PS to load sql2005 DTS pkg

Bruin

SSCrazy

Points: 2085
More actions
July 30, 2018 at 1:57 pm

#355051

Trying to Convert VBScript Com Object to PS.
VbScript:
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer machine,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
objPkg.Execute
PS:
$DTSSQLStgFlag_UseTrustedConnection = 0x100
$dtsapp = New-Object -ComObject DTS.Package
$dtsapp.LoadFromSqlServer(
$server,
[type]::Missing,
[type]::Missing,
$DTSSQLStgFlag_UseTrustedConnection,
[type]::Missing,
[type]::Missing,
[type]::Missing,
$pkgname
)
Throws error.
Exception calling "LoadFromSQLServer" with "8" argument(s): "Error converting data type varchar to uniqueidentifier
Any Help?
Thanks,

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Bruin - Monday, July 30, 2018 1:57 PM
Trying to Convert VBScript Com Object to PS.
VbScript:
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer machine,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
objPkg.Execute
PS:
$DTSSQLStgFlag_UseTrustedConnection = 0x100
$dtsapp = New-Object -ComObject DTS.Package
$dtsapp.LoadFromSqlServer(
$server,
[type]::Missing,
[type]::Missing,
$DTSSQLStgFlag_UseTrustedConnection,
[type]::Missing,
[type]::Missing,
[type]::Missing,
$pkgname
)
Throws error.
Exception calling "LoadFromSQLServer" with "8" argument(s): "Error converting data type varchar to uniqueidentifier
Any Help?
Thanks,

There are quite a few examples available - try one of these approaches:
SSIS and PowerShell in SQL Server 2012
Execute an SSIS Package from Powershell
Run an SSIS package with PowerShell

Sue

Bruin SSCrazy Points: 2085 More actions · Answer 2

Its an older DTS package that has been converted to SSIS.

Bruin SSCrazy Points: 2085 More actions · Answer 3

Its an older DTS package that has "NOT" has been converted to SSIS.

Bruin SSCrazy Points: 2085 More actions · Answer 4

any thoughts on how to convert this script .. without going the SSIS route?

Thanks

Bruin SSCrazy Points: 2085 More actions · Answer 5

I know this is old but any ideas how to make this run?

PS:

$DTSSQLStgFlag_UseTrustedConnection = 0x100

$dtsapp = New-Object -ComObject DTS.Package

$dtsapp.LoadFromSqlServer(

$server,

[type]::Missing,

$DTSSQLStgFlag_UseTrustedConnection,

[type]::Missing,

$pkgname

)

Throws error.

Exception calling “LoadFromSQLServer” with “8” argument(s): “Error converting data type varchar to uniqueidentifier

Bruin SSCrazy Points: 2085 More actions · Answer 6

in the vbscript version you pass in the OLD DTS package and the server

so from a cmd script its csript vbsrun pgkname server