February 5, 2018 at 12:40 am
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...
February 5, 2018 at 1:03 am
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.
February 5, 2018 at 3:29 am
Can you try changing the connections from project level to package level?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 5, 2018 at 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?
February 5, 2018 at 2:48 pm
Scott In Sydney - Monday, February 5, 2018 1:49 PMPerhaps 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply