Wednesday, January 13, 2010

Mind-boggling Problem!

I created an SSIS package that reads a comma delimited (CVS) file from a network share, loads it into a SQL Server table and then moves the file to a folder called Completed. Simple, right? After fighting my battle with the "truncate" error that you could see in my previous post the package runs fine. So it's time to set it up as a SQL job.

I set up the job, run it and it says success but there is no data in the database and the file has not been moved to the Completed folder.

I added logging to the package so that everything will be logged onError, onInformation, and onWarning to the Windows Event log. (You can do that by going to the SSIS>Logging menu item.)

I see that I have a Warning in the event log that the job could not find any files. I check on the permissions and I see that everything is set up right. Both the share permissions and the Security tab shows Modify permissions for the group that the SQL Agent user is in.

I spend all morning with a co-worker of mine (thanks Dennis for your time!) trying all kinds of crazy workarounds and solutions. And what we found that worked is mind-boggling!

If we change the permissions on the share to give Modify rights to the SQL Agent user itself (not just the group it's in) everything works!!!!

Can someone please explain to me why?????

Tuesday, January 12, 2010

SSIS - Annoying Truncation Error

Several of my SSIS projects involve importing data from CSV files. Most of the time if the CSV file contains any text the import will result in the following error in SSIS: "Text was truncated or one or more characters had no match in the target code." I have used a few workarounds to avoid this message. Most of the solutions were posted in a thread on Technet. I will post here the couple of workarounds that has worked for me.

Workaround #1
The Jet engine determines the column types and lengths by the first 8 rows. If after the first 8 rows there are rows that contain text data longer than what is in the first 8 rows, you will get that error. So you can put in a fake row in the very first row with long strings of text. Or move one of the existing real data row with long strings to the first row. It's a bit clumsy but works.

Workaround #2
Convert the CSV file to an Excel workbook. Works like charm!

One other solution has included messing with the registry to tell the JET engine how many rows it should base its guess on the data length. Check out the previous page I posted on the details. I have not tried that but I may have to do it in the future.