Bizzarre SSIS Configuration behavior

I have an SSIS package, (actually many packages at this point because I can duplicate this problem each and every time) with an FTP Task and three connection managers (one for FTP, one for a file share, one OLEDB).

The package in its simplest form, grabs a file from an FTP site and transfers it to the file share. When there are no files found on the FTP site, the FTP task fails as expected.

Here is where it gets strange...I turned on a package configuration, with all variables / properties saved to a SQL table; all except for the OLEDB connection which I am leaving hardcoded in the package for the time being.

The problems I am seeing as soon as I activate the package configuration are twofold: The first occurs with the property "FileUsageType" defined to the file share connection. I have it set to "existing folder" in the SSIS tool, the SQL config table also has a row for this item defined with a value of 2. I have even tried manually changing this value in the SQL config table and the result is always the same error when I run the package:

Error: 0xC002F314 at FTP Task, FTP Task: File usage type of connection "Z:\" should be "FolderExists" for operation "Receive".

If I remove this entry totally from the SQL config table, and leave that property hardcoded in the package then I am able to continue execution in the package and that particular error goes away. But then I come to weird problem #2: I stated above that when there are no files in the FTP directory to retrieve, then the FTP task fails, as I would expect. However, as soon as I activate the package configuration, even when there are no files at the FTP site, the FTP task doesnt fail anymore, it marks itself as successful instead. This is wreaking havoc on my precedence constraints that follow the FTP task naturally. As soon as I deactivate the package configuration, the FTP task behaves normally again.

The problems only seem to happen with config packages stored in SQL. I tested this also with XML file config instead and everything seemed to behave as it should. Unfortunately, I need to store these configs in SQL, not XML.

Does anyone have any ideas on what I need to look at to fix these weird issues?

When you say you have "all variables / properties" saved to a SQL table, what do you mean? Do you really have ALL of them? I'd suggest only storing what you need, if that's the case.|||

Phil,

I have tried it both ways, storing all variables / properties in the SQL table, and just storing only the ones I need (i.e connection strings, file paths, etc). In both cases the outcome is the same as far as the issue with the FTP Task reporting success when it shouldnt. Of course when I store only what I need in the table then the first problem with the "FileUsageType" property, goes away; I can live with keeping that property setting hardcoded in the package. But the success reporting on a task that should fail, and ignoring all my precedence constraints is one I cant so easily work around.

|||

The only thing I can suggest is starting with a configuration-free package that works as desired, and then start configuring one property at a time, testing each step of the way. When you can pinpoint "taking this specific step makes it stop working in this speific way" you'll likely see what's going wrong.

If not, this will make it a lot easier for the folks here to help troubleshoot...