Error when changing the length on DataReader Source

Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.

Under the Component properties tab, the SQL Command looks something like this.

Select ID, ADDRESS, REVISED from ADDRESS

The data type for the source columns are Integer, Varchar(30) and DATE VMS.

Now when I look at the Input and Output properties window,

The External columns has the following data types.

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

The Output columns has the following data types

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

When I tried to change the length of the ADDRESS on the output column, I get the following error.

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.

Any thoughts.

Thanks,
SK

Did you try using a Data Conversion transformation?

Rafael Salas

|||

Hi,
I did try to use the Data Conversion transformation. But when I execute the package, I get the truncation error at the Data Source Reader itself.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "ADDRESS" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039

Thanks,
SK

|||

Hi,
Does anybody have inputs on this issue?

SK

|||

It sounds like the provider you are using is reporting the length of the string incorrectly as 0. I've seen that before with a provider, but I don't recall what one... Is there a different provider you can try?

Otherwise, you might try changing the select statement to cast the value explicitly to a string of length 30 and see if the DataReaderSrc correctly creates the column then.

|||

Unfortunately I can't use any other providers other than ADO.net as I have to use Oracle RDB ODBC to connect to RDB database.

Strangely, now the column is showing as text instead of Unicode string and I was able to use the data conversion transformation to convert back to Unicode String. Not sure what changed on the server. The only thing that I think of is we updated SQL Server with some updated from MS website.

Thanks,
SK

|||

We had the exact same problem trying to pull data from Oracle Rdb. I went round & round but eventually found the solution.

Basically we had to install SQL*Net for Rdb (or OCI Services for Rdb) on our VMS server to make our Rdb database look like an Oracle database. Then we had to install Oracle client software on our client machine. I've used both Oracle 9.2 client and ODAC 10.2. You then set up your client connection using the Oracle Net Manager.

After you've got client & server configured, set up an SSIS connection manager using .Net Providers for OleDb -> Oracle Provider for OLE DB.

It's kind of a hassle to set up but it seems to work just fine.

Regards, Pat

Error when changing the length on DataReader Source

Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.

Under the Component properties tab, the SQL Command looks something like this.

Select ID, ADDRESS, REVISED from ADDRESS

The data type for the source columns are Integer, Varchar(30) and DATE VMS.

Now when I look at the Input and Output properties window,

The External columns has the following data types.

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

The Output columns has the following data types

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

When I tried to change the length of the ADDRESS on the output column, I get the following error.

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.

Any thoughts.

Thanks,
SK

Did you try using a Data Conversion transformation?

Rafael Salas

|||

Hi,
I did try to use the Data Conversion transformation. But when I execute the package, I get the truncation error at the Data Source Reader itself.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "ADDRESS" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039

Thanks,
SK

|||

Hi,
Does anybody have inputs on this issue?

SK

|||

It sounds like the provider you are using is reporting the length of the string incorrectly as 0. I've seen that before with a provider, but I don't recall what one... Is there a different provider you can try?

Otherwise, you might try changing the select statement to cast the value explicitly to a string of length 30 and see if the DataReaderSrc correctly creates the column then.

|||

Unfortunately I can't use any other providers other than ADO.net as I have to use Oracle RDB ODBC to connect to RDB database.

Strangely, now the column is showing as text instead of Unicode string and I was able to use the data conversion transformation to convert back to Unicode String. Not sure what changed on the server. The only thing that I think of is we updated SQL Server with some updated from MS website.

Thanks,
SK

|||

We had the exact same problem trying to pull data from Oracle Rdb. I went round & round but eventually found the solution.

Basically we had to install SQL*Net for Rdb (or OCI Services for Rdb) on our VMS server to make our Rdb database look like an Oracle database. Then we had to install Oracle client software on our client machine. I've used both Oracle 9.2 client and ODAC 10.2. You then set up your client connection using the Oracle Net Manager.

After you've got client & server configured, set up an SSIS connection manager using .Net Providers for OleDb -> Oracle Provider for OLE DB.

It's kind of a hassle to set up but it seems to work just fine.

Regards, Pat

Error using Datareader Destination

Hi guys,
I'm in dire straits here - really hoping someone can help me.

I'm using a datareader destination and consuming it in a C# app. When I fire ExecuteReader on my DTSCommand object to return some data I get the error:

"Could not obtain a datareader object from the specified data flow component"

So we don't have a clue why this isn't working. Although...I'm intrigued by the line about "specified data-flow component". In the C# app you don't actually specify which data-flow component contains the data reader that you want (do you?) so how does it know where to find the datareader? I'm speculating as to whether this is something to do with the error we're getting or not but it'd help to understand anyway.

ANY help at all much appreciated. We're up against it here.

-JamieWell, the CommandText is the NAME of the DataReader Destination in the package, so your app does in fact specify the data flow component...

-Doug
|||The BOL topic on this subject, at least for the 1st Web refresh, is "Loading Data Flow Results into a Client Application." I updated it again just this week with a bit of information on using DtsDataParameter.

Unfortunately the results of attempting to copy and paste a BOL topic are never pretty...but I can try, if you'd like, or simply email you a more recent copy of the SSIS programming hxs file to drop into your BOL folder and give you the latest content (with disclaimer about editing and tech review not completed).

-Doug
|||Hi Doug,
Yeah that's the BOL topic we're using so I fear the problems may be more deep seated than that. We're definately specifying the datareader destination correctly so its not that (I guess by not specifying the data-flow the only requirement is that you don't have 2 datareader destinations with the same name in the same package).

If you could send me an updated version of that doc though that would be fantastic. I think you have my email address don't you?

Very much appreciated.

If anyone out there has some war stories of using the data-flow destination then I'm all ears.

Thanks
Jamie|||Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug
|||

DouglasL wrote:

Any chance the DataReader is timing out before your app attempts to grab it?

Do you have the hex code or symbolic name (DTS_E_*) for the error that you're seeing?

-Doug

I doubt its the timeout cos I set it to 10 seconds (I think) but I'll check it tomorrow.

The error is occurring in the C# app. To all intents and purposes the SSIS package is executing fine so I'm not going to get any DTS_E_* error(s). I'll dig around tomorrow morning though and see what I can find.

-Jamie|||As far as I can determine, the error message that you originally reported is returned ONLY when the DataReader returned to the DtsClient component by the DataReader destination (that is, the instance of System.Data.IDataReader that you expect to contain your data) is null.

At this point, DtsClient has already (1) located the component with the name that you specified in the CommandText and (2) confirmed that it is in fact a DataReader Destination component.

Hope this helps to narrow things down.

-Doug
|||Right,
The package is still failing HOWEVER I have 3 near identical packages which run fine. The calling code is exactly the same, the only difference being the connection string of my DTSConnection object and the name of the datareader.

So, there's obviously something wrong in my package (even though it runs successfully from DTExec) so I delved into the .dtsx file for each package and found the <component>...</component> section for the datareader destination. Apart from the differences in the <inputColumn> elements (cos they're different pipelines) they seem exactly the same so I can't find any problem there.

Hope this makes sense.

So, any further advice on where to search and what to search for?

-Jamie|||Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

|||

Runying Mao wrote:

Jamie,

I might be shooting in the dark (I don't have access to your code and package).

Here is what I'd like to know:
1. Did that package pass if running in BI development studio? (I'm confused that in the beginning you said it fail but you also said it runs successfully from DTExec.)

2. How do you call ExecuteReader? Something similar to the following?

conn = new DtsConnection() as IDbConnection;

conn.ConnectionString = dtexeCmd; -- '-f C:\pkg.dtsx'

conn.Open();

command = conn.CreateCommand();

command.CommandText = dataReaderName;

/* Get the data reader */

reader = command.ExecuteReader();

Is it possible that you share your package and code?

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie|||

Jamie Thomson wrote:

Hiya Runying,
Yeah, that's exactly how we do it.

The package runs successfuly in BIDS and from DTExec. The bit that fails is consuming it from elsewhere.

Can you drop me a mail (jamie.thomson[at]conchango.com) and I'll reply with the package and possibly some data (its sensitive data)

-Jamie

Jamie, I've responded you with my email address.|||

Jamie,

I have found out the cause. Data Reader dest sets its property "DataReader" at validation time. But in your package, Data Flow task has "DelayValidation" set to True. This causes "DataReader" to be NULL at execution time.

It appears something we can improve. I will open a bug.

For now, the workaround is to set Data Flow task property "DelayValidation" to False.

Thanks for your package.

|||Sure, I've got the same problem in my package with a DataReader. In fact, I set the DelayValidation to true due to the query constructed at runtime and got a subject error! I'm in a hurry to try the workaround.
Thank you, Runying!

Sergei Vedischev.