Unable to execute SSIS package thru command line
-
I have SSIS packages which were recently upgraded from 2012 to 2019. I was testing it to see if it would run successfully without any issues. Pretty straightforward package. I have a source and 2 different destination(2 DBs on 2 different DB servers). So when I run the package thru VS, it runs fine. When I run it thru CMD, I get the error 'Could not load package 'Package name' because of the error 0XC001000A: the specified package could not be loaded from the SQL Server database"
The acct which SSIS runs under has admin rights on the SQL Server. I am not sure what I am a missing. Any help or guidance is highly appreciated.
-
I recommend deploying your SSIS packages to SSISDB and running them from there.
Also, did you try this?
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 -
What is your Target server set to in VS?
-
Target server version is set to SQL Server 2019.
We use a third party tool 'ControlM' to create jobs, execute them since we have hundreds of jobs which run everyday so the ask is to run the job thru the tool which calls 'C:\program files (x86)\Microsoft SQL Server\150\DTS\Binn\dtexec.exe'
How it is not working from command line?
I changed the service acct of integration service to run under the acct which has admin rights to all test servers. The job runs successfully when creating an agent job, calling the package but not from command line.
What am I missing?
-
can you give the full command line - change servername and package to something else but meaningful - this so we can see exactly which parms /options you passing to it.
-
"C:\program files (x86)\Microsoft SQL Server\150\DTS\Binn\dtexec.exe" /SQL "PackageName" /SERVER sqlserver /CHECKPOINTING OFF /REPORTING E
I changed the job to point to old server and I no longer run into an issue. All permissions are intact. Could this be because how the package was updated? I have to reach out to a developer who is working on this upgrade project....
- Chitown wrote:
We use a third party tool 'ControlM' to create jobs, execute them since we have hundreds of jobs which run everyday so the ask is to run the job thru the tool which calls 'C:\program files (x86)\Microsoft SQL Server\150\DTS\Binn\dtexec.exe'
The fact that you wish to execute packages from the command line does not mean you are limited to calling dtexec.
I execute packages in SSISDB from the command line by calling SQLCMD to execute a stored procedure which executes a package (passed as a param to SQLCMD). You can read more about that technique here.
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 -
Thanks Phil. The link you shared was very detailed and helpful but unfortunately we don't want to build SSIS catalog. I still don't understand why the job runs when I point to the old server (2012) and fails when I point to the new server (2019). And it is with all the packages. They all fail when running against a new server.
-
Found the issue. There was another folder created under msdb and all the packages were moved there. After I moved couple of packages back to msdb, reran the job and viola.....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply