Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Drammy
Starting Member
13 Posts |
 Posted - 2010-04-27 : 10:45:05
|
| 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 |
|
|
Drammy
Starting Member
13 Posts |
Posted - 2010-04-28 : 04:26:09
|
| 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? |
 |
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2010-04-28 : 09:24:20
|
| Did you check the Progress tab during execution for the converted SSIS package for 2008? It can help you debug on the frontend, more than the back end.Let us know what you find. |
|
|
|
Drammy
Starting Member
13 Posts |
Posted - 2010-04-28 : 16:46:53
|
| Yes, it just says a truncation occured during the bulk insert process. This is going to happen from time to time with the data I am working with, so I capture the error in the stored procedure and handle it there.When I run the stored proc manually it runs to completion, bubbling up the errors as they're handled. When I run the SSIS 2005 package it completes, again bubling up the errors properly. When run in 2008 the SSIS package fails the sql task without reaching completion.I have actually run the step on another system today, again SQL 2008 32 bit. In this scenario the task gets marked as failed, but does complete. Exactly the same package.There must be some setting somewhere that is effecting this.Starting to pull my hair out! |
|
|
|
Drammy
Starting Member
13 Posts |
Posted - 2010-05-12 : 12:30:14
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 14:23:32
|
| As per DOCs I ahve read connection should be using eitherProvider=SQLNCLI10orDriver={SQL Server Native Client 10.0}dunno if that equates to the change you have had to make? |
|
|
|
Drammy
Starting Member
13 Posts |
Posted - 2010-05-13 : 14:32:26
|
| The OLEDB connection manager used Provider=SQLNCLI10 as well as the ADO.NET connectin manager.I haven't changed anything other than the connection manager. |
|
|
|
|
|
|
|
|
SQL 2005 and 2008 differences? -Issue with SQLTask