Data Task creates empty file | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Data Task creates empty file

Hello guys, I have a DTS package that mostly has been running fine for a couple of years now. I didn’t build it but I sorta got stuck maintining it. The end results of the package is a flat file we sned to a 3rd party. There are some update queries that run too. It is used to handling < 5000 records. Last night it had 15,000 dumped on it. This is a first. It didn’t go over well. What ended up happening is that it ran for about 45 minutes, quit without any error message I can find, and the flat file was empty. I ended up having to split the 15k into small chunks and run the thing manually to create small batches instead of the one full one we normally do. This worked but was incredibly tedious. From what I can tell all of the update queries performed succesfully. One of the steps in running the smaller batches was to rollback all of the flags and such the packages uses to recognize new rows. I suspect that the problem came up when the Transform Data Task that dumps into a text file just can’t handle that amount of data that it was trying to pump into the text file. Am I barking up the right tree here? I googled some and couldn’t find any reference to a limitation that would cause this behaviour. Is there a setting I can tweak that would help it handle this load? Any advise would be appreciated. TIA, Jerry
Maybe the output file size is not the problem. I haven’t tried an output file that size, but I have been able to use import files of 60k lines. Maybe a memory bottleneck during the DTS package? Early morning, when I wake up,
I look like Kiss but without the makeup –Robbie Williams, Strong
I had forgotten I posted this. For the archives, it turned out to be an issue with the SAN. My problem was one of the earlier symptoms. We started experiencing greater and greater performance degradation until one night the SAN just went kaputt. If you are running into a similar problem you might want to get your network guy to check the health of your SAN and make sure all of the firmware is patched up.
]]>