When attempting to add new SSIS templates or define templates within a Job source, users may encounter the following errors:
"Option 'reporting' can only be specified once."
or
"The handle is invalid"
So, why does this happen?
JAMS uses a common template for SSIS jobs, found in Basemacros.xml called DTSParsed, which allows users to establish a single standard for the SSIS Execution Method. If a user attempts to enter another method, they may specify the /Reporting attribute twice, or create lines of code not compatible with JAMS.
To resolve the error message, either modify the Job Source or edit the template:
Modify the SSIS Job Source
SSIS code tested on the command line can be used in the source of a JAMS Job with minor alteration, depending on how where the code was built. An example might look something like this:
dtexec /DTS "\"\MSDB\SamplePackage\"" /SERVER MyServer /CHECKPOINTING OFF /REPORTING V /SET
"\"\Package.Variables{User::AuditDB_ConnectionString}.Value"":"\"Data Source=MyServer;Initial Catalog=SampleDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\""
To allow JAMS to read the code in an SSIS Job Source, make the following changes:
- Remove /Reporting value
- Delete dtexec
- Remove escaped quotes. Escaped quotes are: \"
Remove or customize the standardization from the template
- Open Basemacros.xml, usually located at C:\Program Files\MVPSI\JAMS\Scheduler\BaseMacros.xml (or C:\Program Files\MVPSI\JAMS\Agent\BaseMacros.xml if the job is running on the Agent)
- Edit the DTSParsed Macro to conform with your preferred SSIS settings.
Comments