{"id":722,"date":"2014-10-20T07:03:17","date_gmt":"2014-10-20T14:03:17","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=722"},"modified":"2014-10-13T10:52:52","modified_gmt":"2014-10-13T17:52:52","slug":"scheduling-ssis-packages-with-jams-simple-not-easy","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2014\/10\/scheduling-ssis-packages-with-jams-simple-not-easy\/","title":{"rendered":"Scheduling SSIS Packages With JAMS: Simple Not Easy"},"content":{"rendered":"

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). \u00a0Here are a few tips to get you started.<\/p>\n

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:<\/p>\n

dtexec \/DTS \"\\\"\\MSDB\\PhilsSamplePackage\\\"\" \/SERVER DevServ1\u00a0\/CHECKPOINTING OFF \u00a0\/REPORTING V \u00a0\/SET \"\\\"\\Package.Variables[User::AuditDB_ConnectionString].Value\\\"\";\"\\\"Data Source=DevServ1;Initial Catalog=SampleDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\\\"\"<\/pre>\n
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...<\/p>\n
Now create a JAMS job using the execution method \"SSIS\" (not to be confused with \"SSIS Direct\").<\/p>\n
In the source window cut and paste your command line code.<\/p>\n
Now hack your command line to bits so that JAMS will be able to recognize it.<\/p>\n
    \n
  1. Delete the \"dtexec\"<\/li>\n
  2. Remove the escaped quotes. This can be tricky because they look like this \\\"\u00a0<\/strong>do not confuse them with \"\\<\/strong> you need those ones. For example this: \u00a0\"\\\"\\MSDB\\PhilsSamplePackage\\\"\" should be updated in JAMS to\u00a0\"\\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.<\/li>\n
  3. 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:\n
    Option \"reporting\" can only be specified once.<\/pre>\n<\/li>\n<\/ol>\n
     <\/p>\n
    In my case the working JAMS job has the following syntax when you select \"source\":<\/p>\n
    \/DTS \"\\MSDB\\Sample_SSIS_Package\"\r\n\/SERVER\u00a0MyTestServer\r\n\/CHECKPOINTING OFF\r\n \/SET \\Package.Variables[User::AuditDB_ConnectionString].Value;\"Data Source=MyTestServer\\SQL2012;Initial Catalog=SampleDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"\r\n \/SET \\Package.Variables[User::SourceDB_ConnectionString].Value;\"Data Source=MyTestServer\\SQL2012;Initial Catalog=SampleSourceDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"\r\n \/SET \\Package.Variables[User::TargetFilePath].Value;\"\\\\CorpNas1\\ETLFiles\\\"<\/pre>\n
    Test your job and you'll be good to go!<\/p>\n","protected":false},"excerpt":{"rendered":"
    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). \u00a0Here 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,47,43,46],"tags":[157,158,48],"class_list":["post-722","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-etl","category-microsoft-sql-server","category-sql-server-integration-services-ssis","tag-jams","tag-scheduling","tag-ssis"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/722","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=722"}],"version-history":[{"count":4,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/722\/revisions"}],"predecessor-version":[{"id":742,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/722\/revisions\/742"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=722"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=722"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=722"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}