[Collect] SSIS Extract fails with "non-zero error code" – Software Support

Much like it's ancestor (DTS), SSIS failures do not pass any detailed error back to Collect. Instead, you will just see a message talking about a "non-zero error code". The first step you'll want to take in troubleshooting an SSIS failure is running the package manually on the DSP application server. This will almost always expose the underlying error. After that, you can hopefully resolve the root cause of the problem.

Follow these steps to run an SSIS package from the DSP application server:

Make a remote desktop connection to the DSP application server.
Use SSMS to confirm that the target table in SQL Server is empty. If it's not, then you'll need to use a DELETE or TRUNCATE query so that the target table contains 0 rows.
Search for the cmd program, right-click on it, and run it as an administrator.
Enter a command like this:
- ```
dtexec /F "<SSIS File path>\<SSIS File name>.dtsx" /DE <package password>
```
  - <SSIS File path> - Replace this part with the full directory path. If you're not sure where the SSIS packages are created, check out the [Path] setting on the vertical of the "DataGarage_SSIS_FilePath" data source record in the DSP. You will only see this data source on the Admin -> Data Sources page.
  - <SSIS File name> - Replace this part with the name of the package. If you're not sure of the name, check out the [Package Name] setting on the vertical of the table extract record in Collect.
  - <package password> - Replace this part with the DSP's SQL Login password. This should match the password configured on the vertical of the "CranSoft" data source record in the DSP

In some cases, you may find that the SSIS package actually works fine on the DSP application server, even though the "non-zero error code" is returned when it is run from Collect. There are a couple known issues to check if this is the case.

1) If you're using a 6.6.x version of the DSP, then it could be running in 64-bit mode. With the exception of extracts from SQL Server sources, SSIS packages cannot be executed in 64-bit DSP prior to the 6.7.x releases (when the bug was resolved). If this is the case, then we urge you to upgrade to the latest release of the DSP.

If it is impossible for you to upgrade beyond the current 6.6.x version in use, then the only alternative is to place the DSP in 32-bit mode. This can be done by following the steps in the Appendices of the DSP Installation Manual. If 32-bit is also not an option, then we have to ask you to extract with a package type other than SSIS.

2) If there are multiple component versions of SQL Server on the DSP application server, then it may be necessary to update the Windows PATH environment variable. If you are not sure how to update the Windows PATH environment variable (or you are uncomfortable doing so), then please open a support ticket for further assistance. There are many generally available resources online with instructions on updating the Windows PATH environment variable.

When reviewing the PATH environment variable, you should find entries in that look like these:

%SQL Server Path%\150\DTS\Binn\
%SQL Server Path%\140\DTS\Binn\
%SQL Server Path%\130\DTS\Binn\

What you'll need to do is move the correct entry to the top, so it appears before the rest. The correct entry will be the one whose number (130, 140, 150, etc) matches the version of the Integration Services component of SQL Server that is being used on the DSP application server.

You can identify your Integration Services version number by following these steps:

Make a remote desktop connection to the DSP application server.
Navigate to the Control Panel -> Administrative Tools -> Services console.
Sort by [Name].
Scroll down to "SQL Server Integration Services".
Expand the [Name] column so the number at the end can be seen.

For example, if the Integration Services version number is listed as "13.0" in the Services console, then I would update the order of entries in the Windows PATH environment variable to this:

%SQL Server Path%\130\DTS\Binn\
%SQL Server Path%\150\DTS\Binn\
%SQL Server Path%\140\DTS\Binn\

NOTE: After changing the Windows PATH environment variable, you will need to restart the DSP application server. You will not be prompted to do so, but the changes will not have an effect until after a restart.

3) If the Integration Services component of SQL Server on the DSP application server is not the same version as other components installed there, then it may be necessary to adjust these 3 config files (be careful to review and double-check file extensions):

%DSP Install Path%\BOA\DSP\Web\Web.config
%DSP Install Path%\BOA\DSP\Web\bin\CranBerryService64.exe.config
%DSP Install Path%\BOA\DSP\Web\bin\CranBerryService.exe.config

When opened in a text editor (like Notepad), each of them contains a section which looks like this:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.ManagedDTS" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="14.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.DTSRuntimeWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="14.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.DTSPipelineWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="14.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

NOTE: If none of them contain this section, then it means the required components of SQL Server were not installed on the DSP application server at the time when the DSP was installed. In such a case, you'll need to add the entire <runtime> block (shown above) to each file. The whole block (including the <runtime> tag) can be inserted just above the </configuration> closing tag in all three files.

All 3 of the "newVersion" pieces need to be set to the correct number that matches the version of the Integration Services component of SQL Server that is installed on the DSP application server.

You can identify your Integration Services version number by following these steps:

Make a remote desktop connection to the DSP application server.
Navigate to the Control Panel -> Administrative Tools -> Services console.
Sort by [Name].
Scroll down to "SQL Server Integration Services".
Expand the [Name] column so the number at the end can be seen.

For example, if the Integration Services version number is listed as "13.0" in the Services console, then I would update each of the 3 config file sections to this:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.ManagedDTS" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="13.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.DTSRuntimeWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="13.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.DTSPipelineWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="11.0.0.0-14.0.0.0" newVersion="13.0.0.0" />
      <publisherPolicy apply="no" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

NOTE: After changing the 3 config file sections, you will need to restart the DSP application server. You will not be prompted to do so, but the changes will not have an effect until after a restart.

Related articles