There are solutions to similar problems, but none of them quite match this one, and after two programmers researching for three days, it's time to ask.
I have a Task arrangement in Data Flow for an SSIS package that retrieves data from an Oracle database with a simple select. It then has to pass that result to a script task, which then compiles and encrypts the output and places two flat files; one for delivery that is encrypted, and the other internally for auditing purposes.
The Data Conversion task shown here is from an attempted fix, trying to convert all unicode data that is incoming (which is the format the OLE DB Source bound to Oracle receives) to DT_STR strings. However, it has no effect on the actual problem.
The Problem is that while this configuration runs just fine on my computer and a second developer's computer, it does not on a third machine and doesn't on the server, either. When attempting to run this in SSMS on the server where the package will live, it balks, showing the dreaded "Cannot convert unicode to non-unicode strings" for every string column. When exporting the package from the server, this is especially baffling, as the column properties for the OLE DB Source all read as DT_STR instead of DT_WSTR already. Changing the types on the server continues to throw the same error, and it's apparent we're getting no further than "OLE DB Source" before these validation issues occur. Disabling package validation on the server is not an option, as other packages also run on this server.
We have tried:
- The data conversion task you see in the image (it's never reached)
- Manually changing the external and output columns on the OLE DB Source task
- Flipping the ValidateExternalMetaData bit (didn't help)
The fact is, the people who wrote the framework for our SSIS packages are long-gone and the company has no SSIS expert to speak of remaining on staff, and only recently began keeping documentation on projects. Those of us working on this are SQL and .Net developers, so we're the blind leading the blind on this particular subject. We have no-one who would be aware of the environmental differences between the developer machines for each developer and the server itself.
What I'd like to do is make the data source not care about the string format it retrieves and just convert it all in the Data Conversion task, if conversion is even necessary. The script converts the output to ASCII when preparing for encryption anyway. Failing that, what are we missing environmentally, in a property, or fundamentally? At this time, there is no CAST in the SQL command; we're just taking the table data as is, which as I said, "works on my machine" and at least one other, but not on the server.
