July 30, 2014 at 6:30 am
New with SSIS 2012 is the ability to define a project consisting of many packages. I've created one like that and can run it in Visual Studio (SSDT) just fine. What I want to do is set up an agent job for it. It appears that SQL Server Agent 2012 is reluctant to do that.
To run a project from the command line, you use the new "/Package" and "/Project" options. An example would be:
dtexec /Package StartPackage.dtsx /Project "C:\MySolution\MyProject\bin\Development\MyProject.ispac" /CHECKPOINTING OFF /REPORTING V
I can run this from the command line. Now I want to set up an Agent job to do this (I don't want to put the project in SSISDB just yet.) So, I create a new agent job and see that there is no SSIS Project option for job step type. I pick SSIS Package, file system as source, StartPackage.dtsx as the package, then go to the command line tab and replace the options with the ones above. I click OK and get out of the Agent dialog. No errors are given.
However, when I go back in to look at the job again, I see that nothing I entered has been saved! The source has been reset to SSISDB, the package name is gone as are my command line options.
Could this be an oversight in SQL Server 2012's agent?
July 30, 2014 at 6:39 am
If you want to execute packages from the project deployment model through the SQL Server Agent SSIS jobstep, you need to deploy them to the SSIS catalog.
The command line you specified is primarily used in Visual Studio when debugging the packages.
Out of curiosity: what if you specify the command line in the cmdexec job step?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 9:39 am
Koen Verbeeck (7/30/2014)
If you want to execute packages from the project deployment model through the SQL Server Agent SSIS jobstep, you need to deploy them to the SSIS catalog.The command line you specified is primarily used in Visual Studio when debugging the packages.
Out of curiosity: what if you specify the command line in the cmdexec job step?
When i what if you specify the command line in the cmdexec job step, it starts (but hangs at some point, without error or log messages). FWIW the same thing happens sometimes when I run it from the command line though not from VS.
Out of curiosity, have you used agent to schedule packages from a project deployed to SSISDB? The reason I ask is because the Sql Agent Job GUI does not seem to know about projects. This is important for me since I have a project with master and child packages. The execution needs to start with the master which then calls all the children.
July 30, 2014 at 11:32 am
gbritton1 (7/30/2014)
Out of curiosity, have you used agent to schedule packages from a project deployed to SSISDB? The reason I ask is because the Sql Agent Job GUI does not seem to know about projects. This is important for me since I have a project with master and child packages. The execution needs to start with the master which then calls all the children.
Yes I have. When you select SSIS catalog as source for the SSIS package, the GUI adapts itself in the configuration pane so that you can configure project deployment specific options.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 11:52 am
Good to know you're successful!
Here's another thing that doesn't work as I expect:
While in VS, with my Master package in focus, I hit Ctrl-F5. VS does a build then executes the project from that package. Now, due a problem in one of the packages, execution fails. I go to the offending package, fix the problem, return to the master package then hit Ctrl-F5 again. The build fails! Why? Because when VS invoked dtexec.exe and the one package failed, dtexec did not terminate. I have to go to task manager and kill it.
Kinda counter-intuitive!
July 30, 2014 at 1:42 pm
gbritton1 (7/30/2014)
Good to know you're successful!Here's another thing that doesn't work as I expect:
While in VS, with my Master package in focus, I hit Ctrl-F5. VS does a build then executes the project from that package. Now, due a problem in one of the packages, execution fails. I go to the offending package, fix the problem, return to the master package then hit Ctrl-F5 again. The build fails! Why? Because when VS invoked dtexec.exe and the one package failed, dtexec did not terminate. I have to go to task manager and kill it.
Kinda counter-intuitive!
Never had that issue before. Are you sure you stopped debugging before you made the changes to the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2014 at 6:28 am
Koen Verbeeck (7/30/2014)
gbritton1 (7/30/2014)
Good to know you're successful!Here's another thing that doesn't work as I expect:
While in VS, with my Master package in focus, I hit Ctrl-F5. VS does a build then executes the project from that package. Now, due a problem in one of the packages, execution fails. I go to the offending package, fix the problem, return to the master package then hit Ctrl-F5 again. The build fails! Why? Because when VS invoked dtexec.exe and the one package failed, dtexec did not terminate. I have to go to task manager and kill it.
Kinda counter-intuitive!
Never had that issue before. Are you sure you stopped debugging before you made the changes to the package?
It turns out, that if you start the package with Ctrl-F5 instead of just F5, it hangs when an error occurs. That's how I got into that situation. It's worse if you run it from Agent as a command execution and as a scheduled task. Then it hangs and you don't know it. There's nothing in the history (since the job hasn't finished) and nothing in the step log or task output (I enabled both). There's just one instance of DTExec.exe that is a zombie.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply