Scheduling SSIS packages to run via JAMS can be pretty tricky (especially compared to the plug and go of scheduling SSIS with the SQL Server Agent). Here are a few tips to get you started.
Create a command line dtexec for your package to confirm all your parameters and syntax is correct. I like to use the SQL Server Execute Package Utility to make this a bit quicker. Your command line may look something like this:
dtexec /DTS "\"\MSDB\PhilsSamplePackage\"" /SERVER DevServ1 /CHECKPOINTING OFF /REPORTING V /SET "\"\Package.Variables[User::AuditDB_ConnectionString].Value\"";"\"Data Source=DevServ1;Initial Catalog=SampleDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\""
Execute your command line to confirm everything is in working order. Works great on the command line right? Well don’t get cocky and think it’s going to work in JAMS…
Now create a JAMS job using the execution method “SSIS” (not to be confused with “SSIS Direct”).
In the source window cut and paste your command line code.
Now hack your command line to bits so that JAMS will be able to recognize it.
- Delete the “dtexec”
- Remove the escaped quotes. This can be tricky because they look like this \” do not confuse them with “\ you need those ones. For example this: “\”\MSDB\PhilsSamplePackage\”” should be updated in JAMS to “\MSDB\PhilsSamplePackage”. See what I did there? This needs to be done throughout your command line. Don’t forget any variables which you might be passing in.
- Delete /Reporting V if you have it. JAMS will pass this automatically and if you include it you’ll get a message “The handle is invalid”. If you looks at the logfile in JAMS you’ll see the message:
Option "reporting" can only be specified once.
In my case the working JAMS job has the following syntax when you select “source”:
/DTS "\MSDB\Sample_SSIS_Package" /SERVER MyTestServer /CHECKPOINTING OFF /SET \Package.Variables[User::AuditDB_ConnectionString].Value;"Data Source=MyTestServer\SQL2012;Initial Catalog=SampleDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /SET \Package.Variables[User::SourceDB_ConnectionString].Value;"Data Source=MyTestServer\SQL2012;Initial Catalog=SampleSourceDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" /SET \Package.Variables[User::TargetFilePath].Value;"\\CorpNas1\ETLFiles\"
Test your job and you’ll be good to go!