April 27, 2010 at 8:44 am
Hi all,
I have a SSIS package that is failing at a certain stage on SQL 2008 (after being converted). It works fine on SQL 2005.
The failing task is a SQL Task executing a Stored Procedure via an OLEDB connection manager. The stored procedure takes a number of parameters and long story short executes a number of dynamic SQL "BULK INSERT" commands.
I have run SQL Profiler to grab the SQL command being executed. When I run the comman in SQL Management Studio it works fine with any import errors being logged to the file system as specified in the BULK INSERT statement.
When I execute the SQL 2008 version of the SSIS package this particular step fails after about 10 bulk insert errors (truncation and datatype issues). I have maxerrorcount set to 0 everywhere in the package, yet still it fails at the same point.
Like I said, this is different to the behaviour in SQL 2005, where the events get logged to the file system yet the task completes.
Anyone got any ideas what I might be overlooking?
Cheers,
Drammy
April 27, 2010 at 1:44 pm
Any assistance would be greatly appreciated. I am really stumped on this...
Basically SQL 2005 SSIS executes a SQL task which calls a stored procedure to bulk insert some data - any truncation issues get event logged and the process completes successfully.
SQL 2008 SSIS executes the same package, SQL task and stored proc and after abut 10 truncation errors the whole process stops with a task failure.
Same package in both scenarios, maxerrorcount = 0...
Can anyone help?
May 6, 2010 at 11:51 am
Still no replies, anyone???
May 11, 2010 at 1:13 pm
shot in the dark, but have you checked permissions? Whatever name the package is running under will need bulk insert permission.
May 12, 2010 at 10:31 am
Its not permissions - the process starts but just quits when it logs the event...
I think this is a bug with the OLEDB Connection Manager.
I changed the solution to use an ADO.NET connection manager and it worked fine without any further alterations required.
I think it is something to do with when the event is logged.
Very frustrating! Its taken 2 weeks to resolve this issue - I'm surprised no one else has reported it.
May 12, 2010 at 10:39 am
My guess is that IF it is an error then it is an "edge case", where a very specific set of events have to take place and if ANY of them don't you don't have the problem. I had one of those many years ago with SQL 6.5. You have to have all 5 cases to get the error and a change in any one would prevent it.. It happens..
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply