July 24, 2007 at 6:12 pm
I’ve found a strange problem, or bug and I would like to know if anyone else has come across this issue.
I have a SSIS package which does a for/each loop through a list of customers. I execute a stored procedure during the loop which exports records out to csv files into each customer’s folder. So when the process has completed, each customer will have a csv file in its own folder called data_20070630.csv and this process has worked without flaw for months.
Recently, it started failing for the only three customers who have reached record counts over 65000. I haven’t yet verified the exact number, but it's suspiciously close to the Excel Row count limitation of 65536 even though I’m using the proper csv destination code in SSIS.
The failure itself seems odd. Failures occur at different records within the same customer. So a customer with 100,000 records 1st run might fail at record 43150, and the 2nd run might pass the 43150 mark and then fail at record 47200. The written rows look like they are merged right before the process fails.
CustNo CustDate Amount
123456 06/30/07 12345.67
123456 06/30/07 23456.78
123456 06/3.78 123456 06
/30/07 12345.67 123456 06/30/07 123
45.67 123456 06/30/07 12345.6<<process stops here>>
I’ve checked the data and it is properly stored in my db. No commas, or embedded crlf, etc. I’m going do some record stripping to verify the exact record count per customer which causes the failure. I’m also going to write a vb.net process which will mimic the exports. Then I’ll have a secondary verification of the failure, or proof that SSIS is not pumping the data extracts correctly.
I’ll post my results, but I’m interested to know if anyone has had a similar experience.
July 25, 2007 at 1:44 am
If you using a normal flat file destination, the file length would not be a problem.
From the data you gave, I would say the weird contatination is a problem. Stick a data viewer on it and inspect the rows before they fail. Are the rows them self dodge?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 25, 2007 at 8:30 am
My gut reaction is you are butting your head against a size limitation. Depending on the size of each row, you may be writing lines out to the file, but not clearing the memory - if you are using vb, there's a limit to how long the string can be. Once upon a time, it was 64 MB (we're talking 2001, when I had to work around a file size that exceeded the limit - the program read the entire file into a variable and then parsed the file).
If my hunch is good, you will need to watch the length of the variable and find a way to 'flush' it, when it gets large. E.g., every 20,000 records or some such.
Remember, it's a gut feel. I could easily be wrong.
July 26, 2007 at 2:41 am
There's no limit to the text file you can create other than OS limits. I've just created a file with 1 million rows. no problems. Really long data types as well.
You problem has to do with the dodgy data..
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 27, 2007 at 9:18 am
Arrgh. The issue resulted from a semaphore timeout in the connection to a network path on a file server.
After doing a lot of data stripping and testing I still experienced the problem at various records in the csv write process. I tried deleting records, inserting data I knew was proper and reworking the SSIS package. None of this prevented failure.
Finally, I tried exporting the data files directly to c: on the db server. Not only did this work flawlessly, my export time dropped from over 20 minutes down to 47 seconds! I should have realized something was wrong when the exports for 80 files executing against a table with only a million records took so long. But since this is the first time I've used SSIS for this type of process I had no benchmark and just thought it was normal processing speed.
Thank you Steve and Crispin for your input!
-r.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply