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' needs 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

I was changing the data type in both the external column and output column. And I still get the fix mismatch prompt. Maybe I missed something...will look again. How does one change the error column properties since the editor does not allow it. Leaving it as strings is not the answer since the package will not run with the auto-generated types.

Jason 2010-02-05 18:08:04

I looked at your Oracle Select Builder. You run that on the Sql Server side? I guess I use the resulting SELECT statement for the Oracle Source component and then use a script component (or expression variable) and a variable to set the run-time statement.

Jason 2010-02-05 18:16:44

I have never changed the types on the error columns, those are generated by ssis and are not part of the data movement. I will look again and verify which ones I changed when I did it that way. I don't recommend changing those in the advanced editor though. The Oracle Select builder I ran on the SQL side to generate the sql statement to use in the OLEDB source component for each of the tables I was pulling over from Oracle. The sql statement reads the table structure for the local sql table and generates pl/sql for the oracle to pull the data in the correct format.

William Todd Salzman 2010-02-06 04:06:06

What I am getting at overall for this answer it that you should cast or convert in the pl/sql so that the types are in the format you want on the ssis side when it pulls into the dataflow. Let Oracle do the work of conversion so that you don't have to use the advanced editor, or use the Data Conversion component.

William Todd Salzman 2010-02-06 04:11:11

Thank you. I used a modified version of your response. As suggested, I put a query from dual in there that used cast/convert to make sure I get the proper datatypes. I used a package variable to hold the development query and then a script task to update the variable to my runtime query.

Jason 2010-02-08 11:58:39

ansaurus

tags:

views:

answers:

Oracle query as source in SSIS defines wrong data types

related questions