February 8, 2011 at 8:28 am
Hi,
I’m pulling my hair out here. I have setup an ssis package data flow task attempting to take data from a view in one database, and put it in a mapped table in another, the databases are on separate machines but can see each other and have good permissions.
When I run the package it completes with no errors but no data is transferred. When I run the debug it takes forever (same as actually running the package) and source and destination go green after about 30 minutes. I have tried setting the metadata to true and false but still have the same issue.
Can anyone give me any ideas on what the issue could be? I am doing all this in SQL 2005 standard.
Just to add confusion I am doing another one of these on the same servers, the only difference is I’m pulling form a table to a table opposed from a view to a table and that has 60,000 rows works like a dream, and takes a few minutes… It doesn’t figure.
Ideas would be most welcome. Many thanks for looking.
February 8, 2011 at 8:43 am
I'm not sure why you arnt getting data out of the view, I take it you've tested the view in SSMS and its returning data.
Also consider using the 'SQL command' in the data access mode drop down, rather the 'Table or View', as I've experienced some performance issues when using the later method.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 8, 2011 at 8:44 am
On the flow from source to destination (the arrow in the package designer), you can right-click and set a data view on the state of the data at that point. When it hits that stage, it will pop a data grid with all the rows of data as it stands right then. If it's a lot of data, it can be slow, but it's a great debug tool.
Have you tried just querying the view to see what it returns?
Is it possible there's something like an "Intead of Insert" trigger on the target?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 9:41 am
Thanks for your replies.
I have just right clicked on the arrow joining the source and destination and can see the data viewer, after a while i can see the data viewer with data in it. The source and destination were in yellow until i clicked the little triangle button, like the debug start buton in the top left corner of the data viewer, then the source and destination went green. I still don't have any data in my tables or any error to work from. If you have any other idaes please let me know.
Thanks.
February 10, 2011 at 1:08 pm
Hi, I managed to get some errors writting to the logs and wondered if anyone could give me some help with them. There are a few 🙁
Thanks
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:43:10,10/02/2011 16:43:10,0,0x,Beginning of package execution.
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. 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 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. 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 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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. There may be error messages posted before this with more information about the failure.
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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. There may be error messages posted before this with more information about the failure.
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. 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. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. 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. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnTaskFailed,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,0,0x,(null)
OnWarning,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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.
PackageEnd,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,1,0x,End of package execution.
February 11, 2011 at 2:35 am
These are the important ones:
OnError,CRM,CRM\Administrator,Data Flow Task,{25953080-4815-49F6-B918-1DF2D76F34A9},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. 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 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
OnError,CRM,CRM\Administrator,Package,{32833F60-011C-44C1-AE01-DE591DC521B8},{AE9C75BA-F1C0-46E3-9BE6-BE14E2606778},10/02/2011 16:48:22,10/02/2011 16:48:22,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. 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 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
The process is deadlocking. Need to find out why. Are you familiar with the necessary tasks to setup the trace flag and get the deadlock graphs?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 11, 2011 at 3:19 am
Hi, Thanks for that.
I am not familiar with the necessary tasks to setup the trace flag and get the deadlock graphs? I guess I’ll be googling that, please inform me if there is a simple explanation of doing what you suggest, I am desperate to get this working asap.
Thanks again.
February 11, 2011 at 7:07 am
Are you trying to update a table based on a separate call to that same table? That'll result in a deadlock. I've seen that before in SSIS.
If so, you can either move the static copy of the data (the one you won't be updating) into a temp table in an SQL script, and that will unlock the source table, or you can replace that part of the SSIS package with a stored procedure. A proc will only require one connection to the table, so it won't deadlock.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 7:22 am
Bearing in mind i have an ssis package. I have just found this command that i have ran on the destination server in the query window DBCC TRACEON(1204, 1224, 3605, -1) I can't find anything that tells me where i see the errors in detail. If anyone can help i would appreciate it, once ihave done this once i will never forget.
Thanks
February 11, 2011 at 7:40 am
Hi I just posted a message and your came in at the same time. I'm not sure i understand the question, I have two connection one to the view and one to the table. I have the mapping from the view to the table and then i run the package.
I am doing exactly the same thing from table to table on exactly the same servers with exactly the same connections in a different package and that runs like a dream.
I'm not that clued up with all this stuff but i really don't think i made a mistake int eh setting of this up. I was not the creator of the view so there might be an issue there i guess, but as it's just readonly i need i figure that should be ok?
February 11, 2011 at 1:15 pm
With both of those flags enabled, you should be able to find the output of the deadlock in the error log.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 8:58 am
If the view references the table that is being updated, then a lock on the data in the view will block the update. That can result in a deadlock.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2011 at 9:48 am
Thanks, no it does not reference the destination table.
I can see lot's of carrige returns in the source data, i wonder if that is causing me issues?
February 15, 2011 at 6:26 am
The problem is a deadlock. Carriage returns won't cause that. You need to find out what the deadlock is being caused by.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2011 at 4:47 am
Ok, after some playing i have got more detailed errors out of this deadlock. I tyhink they are different from before, i am posting them now and am also going through it. If anyone can see anythign obvious please let me know.
Thanks in advance.
Errors
OnErrorCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}There was an error with input column "C_achieve" (559) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}There was an error with input column "C_achieve" (559) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (29)" failed because error code 0xC0209077 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. There may be error messages posted before this with more information about the failure.
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (29)" failed because error code 0xC0209077 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. There may be error messages posted before this with more information about the failure.
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_PROCESSINPUTFAILED. 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. There may be error messages posted before this with more information about the failure.
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_PROCESSINPUTFAILED. 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. There may be error messages posted before this with more information about the failure.
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
16/02/2011 11:37:1816/02/2011 11:37:18
OnErrorCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
16/02/2011 11:37:1816/02/2011 11:37:18
OnTaskFailedCRMCRM\AdministratorData Flow Task{25953080-4815-49F6-B918-1DF2D76F34A9}{0A292E65-3966-4E42-810E-AF6857A821DC}16/02/2011 11:37:1816/02/2011 11:37:18
OnWarningCRMCRM\AdministratorPackage{32833F60-011C-44C1-AE01-DE591DC521B8}{0A292E65-3966-4E42-810E-AF6857A821DC}SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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.
16/02/2011 11:37:1816/02/2011 11:37:18
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply