DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE with OLEDB Command

Hello,

I have an issue with the OLEDB Command in a package of mine where it used to work and now it doesn't. I had 4 different packages where I was bringing in a flat file, massaging the data, and calling 1 of 4 Stored procedures using the OLEDB Command. Everything was working great, then we decided to use the SSIS EBCDIC conversion in the flat file connection rather than converting to ASCII outside of SSIS. This wasn't a problem for 3 of the 4. The one I have an issue with seems to be somehow corrupt, I keep getting the error "Invalid character value for cast specification", as well as "DBSTATUS_UNAVAILABLE", for a particular column. When I re-map that column in the OLEDB Command task, re-run the package, it then tells me a different column has the exact same error. So in going through and re-mapping all of the columns, it goes back to the 1st column I had an error with and gives me the exact same error... ARRRGGGGHHHH!!!!

It seems like something is corrupt in SSIS or something. Does anyone have any thoughts, other than re-writing the package from scratch?

I know there's no issue with the data, I imported the same file into a test table without problems.

Hi, I solved my problem.

When I switched over to using EBCDIC instead of ASCII, normally I get an error for any string values coming in that I need to convert them from Code Page 37 to Code Page 1252. Also, normally I get an error anytime I'm trying to insert a value into a column if I have a different datatype on the input as opposed to the column, i.e. my input is a string and the column is an Integer. I wasn't getting an error for this, in fact I was getting error's for other columns that didn't have any issues. Perhaps this is because i'm calling a Stored Procedure rather than using a SQL Server destination, as I never seem to have this problem with a SS Destination.

Very weird and error messages were not intuitive at all. Oh well, at least it's resolved.

Andy

|||

Ok.. finnaly, what did you made for fix this?

I got the same problem but using a dataconversion from NUMERIC to STR

|||I only got the problem for strings. Just create another column with that value and make the new column ascii.|||

If you decide to do EBCDIC conversion in SSIS at the beginning of your stream you might want to check out the following component.

http://www.aminosoftware.com

It is a custom source component which allows you to do the EBCDIC conversion (including packed decimal, occurs, redefine, etc) all within the source component so that what comes out is ASCII columns.

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" 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 Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, 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 Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" 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 Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, 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 Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!