August 25, 2008 at 1:25 pm
Hello Friends,
I have an Excel Source and a SQL Server Destination. For one of my SQL Server Destination table, I have to get data from three different Excel files. So, I decided to have all these three different Excel sources in a single package. As a result, I created 3 data flow tasks in a single control flow component. So, I have one package, which has three data flow tasks in it. DFT1, DFT2, DFT3. Now, when I am trying to execute this package, one of the three Data Flow task fails everytime. Sometimes, DFT1 package fails, sometimes DFT2 and sometimes DFT3 fails.
The error that I am getting is:
[SQL Server Destination [131]] Error: 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 103) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
And when I rerun the transaction, then again one of these three data flow tasks fails.
I would really appreciate if there is anyone who can give me some knowledge regarding solving this issue.
Thank you,
notes4we
August 25, 2008 at 2:34 pm
Are you using Fast Load in the destination with table lock? If you are I'd go away from that as the tasks are trying to run in parallel and causing the deadlock.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 2:48 pm
Hello Jack Corbett,
Thank you so much for your response.
Yes, it is working now. I had to go to the Properties window of my SQL Server Destination and change the BulkInsertTablock option to False. It was set as true before.
Thank you once again.
I had one more question. For instance, in my excel source, I have around 10 rows. When I try to use the Data Flow Task having, excel source, derived column transformation and sql server destination, then surprisingly I find 20 rows transferred to my destination. I went back and checked my excel source, but there were only 10 rows. The rest of the excel sheet was blank. So, I selected few empty cells at the bottom and right clicked it and deleted the rows. I thought it will not matter much, but after doing this I was able to get only 10 rows.
I wonder what is hidden in those blank cells in Excel sheet. Now, my question is, it is not possible for me to practically clean all the excel sheets every time. And there is no use of the null values that I am getting in my destination too. So, do you know any way where I can control the limit of rows that I want as my output. In a sense, in Excel source if I can set limit for transferring only limited number of rows, then my problem will be solved.
Thanks,
- notes4we 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply