SSIS Indirect Configurations

With indirect configurations SSIS enables you to store the path to the dtsConfig file in an environment variable.  At runtime the package looks up the location to the config file, load the config file at that location, and does whatever it needs to do with the information.

If your SSIS package gets executed from a SQL Job, it is important to restart the SQL Agent after creating the environment variable - the SQL Agent caches the environment variables and won't pick up new ones unless restarted.

Protocol error in TDS stream

Yesterday I struggled with the following issue in SSIS: I specified a stored proc as the SQL command text in an OLE DB Source in a data flow. When I clicked the Preview button the data I expected was returned. The Columns were also picked up successfully in the source editor.

But everytime I ran the package it failed with the following errors:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Protocol error in TDS stream"...

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1135) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure...

A component has returned from its PrimeOutput call. : 1135 : OLE DB Source
SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited...

SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled...

SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited...

After searching the whole day for a solution I finally stumbled accross this post by Adam Machanic. At first sight it did not fit my problem, but out of desperation I decided to give it a bash; and it worked.

I am not going to repeat what he said in his post because he did a brilliant job of it. But in short: tables, views and table-valued UDFs have explicitly defined output columns; and stored procs don't. So SSIS will have guaranteed success in retrieving the correct output columns from tables, views and table-valued UDFs, while a stored proc can return different resultsets based on the input parameters.

As I said before, this does not fit my problem since SSIS did pick up the correct output columns, but I suspect that it missed some crutial metadata which wasn't available in the stored procedure.

Multiple SSIS instances on the same machine

You can only have one instance of SSIS (SQL Server Integration Services) installed on a machine at a time.

But you can have multiple SSIS database instances on one machine at the same time. See this post on how to do that.