Using dtexec with /file parameter - connection not found

  • Most of the time I deploy my projects to SQL Server, and my Powershell script which calls dtexec works.

    This time, I've got a small SSDT project that I don't want to deploy, but I want to run all the packages using dtexec, so I can script it to run unattended.  Asynchronous execution would be great, synchronous would be acceptable.  I'll just let it run overnight.

    In Powershell, I've cd'd to the directory containing the packages:

    PS Y:\SSIS\RLDX - Tmp\RLDX - Tmp> ls FACILITY.dtsx, *.conmgr

      Directory: Y:\SSIS\RLDX - Tmp\RLDX - Tmp

    Mode      LastWriteTime  Length Name
    ----      -------------  ------ ----
    -a---   05/02/2018 6:06 PM  414220 FACILITY.dtsx
    -a---   05/02/2018 5:09 PM   459 Src_APDC.conmgr
    -a---   05/02/2018 5:09 PM   463 Src_HIEREP.conmgr
    -a---   05/02/2018 5:09 PM   464 Tgt_Extract.conmgr

    The FACILITY package uses Src_HIEREP.conmgr for the source, and Tgt_Extract.conmgr for the target connections.

    But I get this error:

    PS Y:\SSIS\RLDX - Tmp\RLDX - Tmp> dtexec /f .\FACILITY.dtsx
    Microsoft (R) SQL Server Execute Package Utility
    Version 12.0.5000.0 for 32-bit
    Copyright (C) Microsoft Corporation. All rights reserved.

    Started: 6:27:35 PM
    Error: 2018-02-05 18:27:35.99
     Code: 0xC001000E
     Source: FACILITY
     Description: The connection "{D353BF79-A8C5-4C37-8322-A5CDE398A3A4}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
    End Error
    Progress: 2018-02-05 18:27:36.04
     Source: Data Flow Task
     Validating: 0% complete
    End Progress
    Progress: 2018-02-05 18:27:36.04
     Source: Data Flow Task
     Validating: 10% complete
    End Progress
    Error: 2018-02-05 18:27:36.04
     Code: 0xC020801B
     Source: Data Flow Task OLE DB Destination [13]
     Description: The runtime connection manager with the ID "{D353BF79-A8C5-4C37-8322-A5CDE398A3A4}" cannot be found. Verify that the connection manager collection has a connection manager with that ID
    .
    End Error
    Error: 2018-02-05 18:27:36.04
     Code: 0xC0047017
     Source: Data Flow Task SSIS.Pipeline
     Description: OLE DB Destination failed validation and returned error code 0xC020801B.
    End Error
    Progress: 2018-02-05 18:27:36.04
     Source: Data Flow Task
     Validating: 20% complete
    End Progress
    Error: 2018-02-05 18:27:36.04
     Code: 0xC004700C
     Source: Data Flow Task SSIS.Pipeline
     Description: One or more component failed validation.
    End Error
    Error: 2018-02-05 18:27:36.04
     Code: 0xC0024107
     Source: Data Flow Task
     Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started: 6:27:35 PM
    Finished: 6:27:36 PM
    Elapsed: 0.812 seconds
    PS Y:\SSIS\RLDX - Tmp\RLDX - Tmp>

    I've read the below but can't see what I need to set (/conn option?) to get this to work.

    https://technet.microsoft.com/en-us/library/ms162810%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
    https://technet.microsoft.com/en-us/library/ms140203%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
    https://technet.microsoft.com/en-us/library/ms141682%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    https://stackoverflow.com/questions/7688700/how-to-pass-a-connection-string-into-an-ssis-file

    The SO post is a bit worrying...please tell me dtexec isn't so braindead that I have to manually specify what is already saved in the *.conmgr file!

    Thanks...

  • After posting my OP, I found this:  https://stackoverflow.com/questions/31388748/ssis-project-connections  (which is nowhere in the documentation)

    But dtexec /package FACILITY.dtsx /project '.\bin\Development\RLDX - Tmp.ispac'

    Just flashes a command window, says the package has started, but doesn't actually load the table.

  • Can you try changing the connections from project level to package level?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Perhaps I could but I don't want to.  If dtexec is so braindead that it can't execute a file system package ala SSDT, then I'll just deploy the project, run the packages, and delete the project.

    So..."can dtexec execute a file system package created in a project containing project level connection managers?"  If not, is there a good reason why?

  • Scott In Sydney - Monday, February 5, 2018 1:49 PM

    Perhaps I could but I don't want to.  If dtexec is so braindead that it can't execute a file system package ala SSDT, then I'll just deploy the project, run the packages, and delete the project.

    So..."can dtexec execute a file system package created in a project containing project level connection managers?"  If not, is there a good reason why?

    Do that.
    No idea about dtexec and file system projects, because I always deploy to SSISDB.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 5 posts - 1 through 4 (of 4 total)

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