Unable to execute SSIS package thru command line

Question

Post reply

Unable to execute SSIS package thru command line

Chitown

Hall of Fame

Points: 3390
More actions
September 20, 2022 at 8:27 pm
Go to Answer
#4090011

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.
Chitown

Hall of Fame

Points: 3390
More actions
September 29, 2022 at 2:02 pm
Answer
#4093444

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

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

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

wburke 85918 SSChasing Mays Points: 619 More actions · Answer 2

wburke 85918

SSChasing Mays

Points: 619

September 22, 2022 at 12:35 pm

#4090780

What is your Target server set to in VS?

Untitled

Chitown Hall of Fame Points: 3390 More actions · Answer 3

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?

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

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.

Chitown Hall of Fame Points: 3390 More actions · Answer 5

"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....

This reply was modified 2 years, 3 months ago by Chitown.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 6

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

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

Chitown Hall of Fame Points: 3390 More actions · Answer 7

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.