SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using dtexec with /file parameter - connection not found


Using dtexec with /file parameter - connection not found

Author
Message
Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 756
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...

Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 756
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217235 Visits: 24928
Can you try changing the connections from project level to package level?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Scott In Sydney
Scott In Sydney
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 756
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217235 Visits: 24928
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search