Deadlock Issue during Data Flow Task-Execution

Hi, folks!

I got a serious problem with an SSIS-Import. My packages import from a foreign source into a kind of temp-table (actually it's not a temporary table, it′s just filled with data and truncated after completion of the package), do some transformations and then I got a data flow task that simply copies all the rows from the "temp" to the final table. I get the following errors (here there are two simultanious copy operations from two different "temps" into the same final table.

Error: 0xC0202009 at _temp to finaltable 5 2 1, OLE DB Destination [16]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 5 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (16) 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 _temp to finaltable 5 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 5 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 5 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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 _temp to finaltable 5 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at _temp to finaltable 5 2 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DF at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has started.

Information: 0x402090E0 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has ended.

Information: 0x40043009 at _temp to finaltable 5 2 1, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at _temp to finaltable 5 2 1, DTS.Pipeline: "component "OLE DB Destination" (16)" wrote 5041 rows.

Task failed: _temp to finaltable 5 2 1

Warning: 0x80019002 at KDStat_alles_412: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: 412

Warning: 0x80019002 at kdstat_alles_master: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: 0xC0202009 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: The "input "OLE DB Destination Input" (4481)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (4481)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 1 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (4468) 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 _temp to finaltable 1 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 1 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 1 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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 _temp to finaltable 1 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Well, it looks like those two processes simply deadlock each other. But there are 11 Simultanious Data Imports which all go fine. The Issue only occurs at those two. The structure of the packages is exactly the same everywhere.

Is it possible that a previous update-sql query hasen′t committed properly and locks the datasets?

I have seen similar behaviour. Check your OLE DB Destination and make sure Table Lock is switched off. It causes a table lock on your destination - probably for performance reasons. If you do simultaneous copy to dest table, this could be the reason.

According to BOL this is switched OFF by default, but in my experience the default is ON.

Regards,

Pipo

|||

Uuuhm, jepp...

This seems to work. Gotta make a few tests, but yes. Thanks a lot, Pipo!

|||

Hi Pipo1,

I was readong your comment to someone about the Deadlock issue during Data Flow task - Execution.

I am having a similar situation.

I am very very new to SSIS, and how do I find out about the table lock on OLEDB Destination.

Please help!!!

Thanks in tons,

Meena.

|||Meena,
Double click on the OLE DB Destination, and if using fast load, you'll have a table lock option.|||

Thank You Phil.

I got the issue resolved.

On a separate note, I am having some issues with the maintenance plans of backing up my db on sql 2005.

My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

I am doing the following in my maintenance plan:

check db integrity

shrink db

re-organize index

update statistics

backup db

maintenance cleanup

The db uses SIMPLE recovery model.(Earlier it was set to be on FULL recovery model).

Any help is appreciated!

Thanks,

Meena.

Deadlock Issue during Data Flow Task-Execution

Hi, folks!

I got a serious problem with an SSIS-Import. My packages import from a foreign source into a kind of temp-table (actually it's not a temporary table, it′s just filled with data and truncated after completion of the package), do some transformations and then I got a data flow task that simply copies all the rows from the "temp" to the final table. I get the following errors (here there are two simultanious copy operations from two different "temps" into the same final table.

Error: 0xC0202009 at _temp to finaltable 5 2 1, OLE DB Destination [16]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 5 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (16) 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 _temp to finaltable 5 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 5 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 5 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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 _temp to finaltable 5 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at _temp to finaltable 5 2 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DF at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has started.

Information: 0x402090E0 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has ended.

Information: 0x40043009 at _temp to finaltable 5 2 1, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at _temp to finaltable 5 2 1, DTS.Pipeline: "component "OLE DB Destination" (16)" wrote 5041 rows.

Task failed: _temp to finaltable 5 2 1

Warning: 0x80019002 at KDStat_alles_412: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: 412

Warning: 0x80019002 at kdstat_alles_master: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: 0xC0202009 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: The "input "OLE DB Destination Input" (4481)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (4481)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 1 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (4468) 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 _temp to finaltable 1 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 1 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 1 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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 _temp to finaltable 1 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Well, it looks like those two processes simply deadlock each other. But there are 11 Simultanious Data Imports which all go fine. The Issue only occurs at those two. The structure of the packages is exactly the same everywhere.

Is it possible that a previous update-sql query hasen′t committed properly and locks the datasets?

I have seen similar behaviour. Check your OLE DB Destination and make sure Table Lock is switched off. It causes a table lock on your destination - probably for performance reasons. If you do simultaneous copy to dest table, this could be the reason.

According to BOL this is switched OFF by default, but in my experience the default is ON.

Regards,

Pipo

|||

Uuuhm, jepp...

This seems to work. Gotta make a few tests, but yes. Thanks a lot, Pipo!

|||

Hi Pipo1,

I was readong your comment to someone about the Deadlock issue during Data Flow task - Execution.

I am having a similar situation.

I am very very new to SSIS, and how do I find out about the table lock on OLEDB Destination.

Please help!!!

Thanks in tons,

Meena.

|||Meena,
Double click on the OLE DB Destination, and if using fast load, you'll have a table lock option.|||

Thank You Phil.

I got the issue resolved.

On a separate note, I am having some issues with the maintenance plans of backing up my db on sql 2005.

My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

I am doing the following in my maintenance plan:

check db integrity

shrink db

re-organize index

update statistics

backup db

maintenance cleanup

The db uses SIMPLE recovery model.(Earlier it was set to be on FULL recovery model).

Any help is appreciated!

Thanks,

Meena.

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.