Convert VbScript Com object to PS to load sql2005 DTS pkg

  • Bruin

    SSC Eights!

    Points: 894

    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,

  • Sue_H

    SSC Guru

    Points: 89892

    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

    SSC Eights!

    Points: 894

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

  • Bruin

    SSC Eights!

    Points: 894

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

  • Bruin

    SSC Eights!

    Points: 894

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

     Thanks

  • Bruin

    SSC Eights!

    Points: 894

    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,

    [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

  • Bruin

    SSC Eights!

    Points: 894

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

    so from a cmd script its csript vbsrun pgkname server

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

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