Oracle query as source in SSIS defines wrong data types

I have a somewhat complex query that I want to use as a source in an SSIS package. I create my OLE DB Source, specify the access mode as SQL, and paste my query in the command textbox. When I click Preview, sample data comes back and everything looks good.

However, when I try to run the package I get back "external columns are out of sync with data source columns" and a mess of "external column 'xxx' ne开发者_StackOverfloweds to be removed from the external columns" error messages. Note that in design mode there are no entries in the Error List tab.

Doing my research I see that external, output, and error columns are being automatically defined with the wrong data types. Some obvious integer columns are being defined as strings whereas others are set as unicode strings.

I tried to manually fix them via the advanced editor but it seems that you cannot change the error columns. However, if I fix the external and output source columns I get that message about being out of sync...do you want it fixed? I click yes and it puts it back to strings...

Thanks for any insight.

---

The problem you are having can be solved from one of several methods.

First if you are trying to use the advanced editor to change this (which I recommend against, as it is difficult for maintainers to see what you have done), you must change the settings for both the external and output source columns in sync. What it is 'Fixing' when you click yes is the mismatch between the two.

Second, you can leave them all as strings and use the Data Conversion component to convert to the types you need. This is easier for future maintainers.

I prefer to use an oracle sql statement that sets the types I need in the pl/sql so that SSIS creates the fields with the proper types. That allows the definition to be explicit so maintainers can see what was done. To that end I use a sql query to build my oracle select statement from the structure of the destination table (In the project I built this for, we bring all the data/structure intact from Oracle into a staging Sql Server machine before transformations are applied.)
https://docs.google.com/leaf?id=0B4aVrSS2ke2IZGVkYWJkOWYtY2Y3Yy00MDI5LTkyMDctYjgwMGY2YzZiODRm&hl=en