locked
convert vbs to ps RRS feed

  • Question

  •  

     I'm trying to use this script to load SQL DTS packages. This same function works with a different syntax using
    vbscript that I'm trying to convert. These DTS packages are stored in msdb and are not dtsx.

    $pkgname = "run_index_build"
    $server = "localhost"
    [reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
    function getPackage([string]$pkgname,[string]$server)
    {
    	$dtsapp = New-Object ("Microsoft.SqlServer.Dts.Runtime.Application")
    	$pkg = $dtsapp.LoadFromSqlServer("\$pkgname",$server,$null,$null,$null)
    	return $pkg
    }
    
    $pkg = getPackage $pkgname,$server

     Throws error

     
    Exception calling "LoadFromSqlServer" with "5" argument(s): "The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
    "
    At :line:7 char:33
    +     $pkg = $dtsapp.LoadFromSqlServer <<<< ("\$pkgname",$server,$null,$null,$null)

      Thanks.

    Thursday, June 28, 2018 11:03 PM

Answers

  • The issue with DTS COM is that it is obsolete.  I have no way to test this without big issues installingold tools.  MS discontinued DTS many years ago.  All documentation has been removed from the MSDN site.  You should convert your packages to DTSX or SSIS.


    \_(ツ)_/

    • Marked as answer by hart60 Tuesday, July 3, 2018 12:26 AM
    Monday, July 2, 2018 7:33 PM

All replies

  • If you are trying to execute a package try using DTExec

    \_(ツ)_/

    Thursday, June 28, 2018 11:14 PM
  •  I would like to try and get this script working as I have a bunch of packages I could
    just swap the ps script instead of the vbs. Does that message indicate security to run the DTS?

      Thanks.

    Thursday, June 28, 2018 11:19 PM
  • [reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
    function getPackage([string]$pkgname,[string]$server){
    	$dtsapp = New-Object Microsoft.SqlServer.Dts.Runtime.Application
    	$dtsapp.LoadFromSqlServer($pkgname,$server.$null,$null,$null)
    }
    $pkgname = '<path to package>run_index_build'
    $server = 'localhost'
    $pkg = getPackage $pkgname,$server

    You are getting a timeout.

    You need to use the full path to the package including drive.


    \_(ツ)_/


    • Edited by jrv Thursday, June 28, 2018 11:33 PM
    Thursday, June 28, 2018 11:22 PM
  •  On the older dts packages there isn't any file name to reference. I think these DTS packages
    are 2005 version created. 

     Thanks.

    Friday, June 29, 2018 2:05 AM
  •  This is what the vbs looks like to execute the DTS.

     Set objPkg = CreateObject("DTS.Package")
     objPkg.LoadFromSQLServer machine,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
     objPkg.Execute

     Thanks.

    Friday, June 29, 2018 9:32 AM
  • COM is not the same thing as Net classes.

    Your issue is a timeout which indicates a connectivity or configuration issue.

    As I posted above.  Use DTExec to execute a package.  It works in PowerShell b just typing it in.

    DTExec /?


    \_(ツ)_/

    Friday, June 29, 2018 9:37 AM
  •  Okay I looked at Dtexec, but with these older DTS packages there is no PATH(file) to run. In the
    vbs and COM you just passed in machine and the package name.

    Thanks.

    Friday, June 29, 2018 9:48 AM
  • The name is the full path to the package.  It is rooted but may be in a folder.

    $packagename = '\mypackage'

    This is a package at the root.

    The error says you have a connectivity issue.   That is not a scripting issue.  You need to try with DTExec to test connectivity.

    Post in SQLServer forum to learn how to test the connectivity.  DTExec is the easiest way.


    \_(ツ)_/

    Friday, June 29, 2018 9:52 AM
  •  It was a configuration issue which now is resolved, but now it throws this error from the script
    you supplied. These DTS packages havn't went thru the SSIS conversion they are the old format
    and so at this point Dtexec wasn't an option.

     Cannot find an overload for "LoadFromSqlServer" and the argument count: "4"


     Thanks.

    Friday, June 29, 2018 1:09 PM
  •  

     I have been trying to hunt down this error message with no success and ideas?

      Can this not be converted form VBS using the COm Object.

    Set objPkg = CreateObject("DTS.Package")
     objPkg.LoadFromSQLServer machine,,,DTSSQLStgFlag_UseTrustedConnection,,,,packageName
     objPkg.Execute

    $dtsapp.LoadFromSqlServer($pkgname,$server,$null,$null,$null)

     Thanks.

     

    Monday, July 2, 2018 6:10 PM
  • $dtsapp.LoadFromSqlServer($pkgname,$server,[type]::Missing,[type]::Missing,[type]::Missing)


    \_(ツ)_/

    Monday, July 2, 2018 6:13 PM
  •  Throws error:

    Cannot convert argument "events", with value: "System.Reflection.Missing", for "LoadFromSqlServer" to type "Microsoft.SqlServer.Dts.Runtime.IDTSEvents": "Cannot convert
    the "System.Reflection.Missing" value of type "System.Reflection.Missing" to type "Microsoft.SqlServer.Dts.Runtime.IDTSEvents

     Thanks.

    Monday, July 2, 2018 6:28 PM
  • $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
    )


    \_(ツ)_/

    Monday, July 2, 2018 6:51 PM
  •  error thrown:

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

     Thanks.

    Monday, July 2, 2018 7:21 PM
  • The issue with DTS COM is that it is obsolete.  I have no way to test this without big issues installingold tools.  MS discontinued DTS many years ago.  All documentation has been removed from the MSDN site.  You should convert your packages to DTSX or SSIS.


    \_(ツ)_/

    • Marked as answer by hart60 Tuesday, July 3, 2018 12:26 AM
    Monday, July 2, 2018 7:33 PM
  •  ok thanks for the replies.

      If you do a lot of file(csv) loads to SQL some with headers and other not would it be better to use PS

    to do the ETL or ssis. If you have a script its a little more visible than having to have sql tools to create\support data loading.

     

    Tuesday, July 3, 2018 12:26 AM
  • You can build an SSIS package to load a CSV in about 5 minutes.

    You can also just define a powershell task in SQS that loads a CSV.


    \_(ツ)_/

    Tuesday, July 3, 2018 12:43 AM