We have an SSIS package at work, which was upgraded from a DTS package, basically line for line (yeah i know) My question is if there's a reason why a certain update statement doesn't seem to run when the SSIS pkg is run as a whole, but works if we run it task by task, or if we run the query straight out of mgmt studio. I'm much more interested in the WHY (i anticipate most replie would be just re-write it, but i want to know WHY it doesn't work) Here's the sanitized query, more or less. DECLARE @ICount INT DECLARE @somecounter INT DECLARE @some_id UNIQUEIDENTIFIER SET @ICount = 0 SET @somecounter = 0 DECLARE csr CURSOR STATIC LOCAL FOR SELECT sow.Id FROM table1 so INNER JOIN table2 sob ON sob.table1Id = so.Id INNER JOIN Customer soc ON soc.table1Id = so.Id INNER JOIN Customer c ON c.Id = soc.CustomerId INNER JOIN table3 sopn ON sopn.table1Id = so.Id INNER JOIN table4 sowo ON sowo.table1Id = so.Id INNER JOIN table5 sow ON sow.table6Id = sowo.Id LEFT OUTER JOIN table6 wo ON wo.Id = sowo.table6Id WHERE .... OPEN csr FETCH NEXT FROM csr INTO @some_id WHILE @@fetch_status = 0 BEGIN INSERT INTO stagingtable (col1,col2,col3) SELECT a_bunch_of_columns FROM table1 so INNER JOIN table2 sob ON sob.table1Id = so.Id INNER JOIN table1_Customer soc ON soc.table1Id = so.Id INNER JOIN table4 sowo ON sowo.table1Id = so.Id INNER JOIN table5 sow ON sow.table6Id = sowo.Id left outer JOIN table6 wo ON wo.Id = sowo.table6Id INNER JOIN table3 sopn ON sopn.table1Id = so.Id INNER JOIN PartNumber pn ON pn.Id = sopn.OemPartNumberId INNER JOIN Customer c ON c.Id = soc.CustomerId WHERE sow.Id = @some_id SET @ICount = @ICount + 1 INSERT INTO stagingtable (col1,col2,col3) SELECT a_bunch_of_columns FROM table1 so INNER JOIN table4 sowo ON sowo.table1Id = so.Id INNER JOIN table5 sow ON sow.table6Id = sowo.Id left outer JOIN table6 wo ON wo.Id = sowo.table6Id WHERE sow.Id = @some_id SET @ICount = @ICount + 1 SET @somecounter = @somecounter + ( SELECT xyz FROM table5 WHERE Id = @some_id ) UPDATE table5 SET somedate = GETDATE() WHERE Id = @some_id AND somedate IS NULL AND xyz IS NOT NULL -- Get the next record FETCH NEXT FROM csr INTO @some_id END CLOSE csr DEALLOCATE csr DECLARE @temp VARCHAR(100); SET @temp = dbo.MoneyToComp3(ISNULL(@somecounter, 0)) SET @temp = REPLACE(@temp, '.', '') SET @ICount = @ICount + 1 INSERT INTO stagingtable(col1,col2,col3) SELECT a_bunch_more_columns

Does the task show in green when the package is run in BIDS? Do you have logging turned on? Have you run a SQL Profiler trace when the package is being executed? Talk to me now on

Yes, the package does turn green when run individually and as part of the entire package. Unfortunately, I'm helping out a co-worker and it's been difficult to get full cooperation. I asked if they turned on package logging (if that's what you're referring to). The answer was YES, but when I ask what did they see.. the answer gets fuzzy. No on SQL profiler trace (and would be difficult to coordinate with the DBA :/) The same co-worker found a snippet that mentions "When you use OLE DB and ADO connection managers, the task ignores the result sets that occur after the first result set. Therefore, with these connection managers, the task ignores an error returned by an SQL command or a stored procedure when the error is not part of the first result set." Could that be what's happening here, if I interpreted it correctly? Could have caused a false "green" and hid any error message?

Just to add my 2C: I have seen a number of packages performing badly (errors, too) if they had cursors embedded into Execute SQL Task set to run in a SQL Agent job. Is this the case with you halokitty?Arthur My Blog

That's possible. If you can't get this to work as you expect, it might be time to recode it into something more in line with SSIS' design - OR - place the code into a stored proc and call the stored proc from SSIS. Talk to me now on

I'm trying to get my hands on a copy that went into production and see if I can still repro. Clues about what happened in the job (yes, it's precisely a cursor stuff'd into SQL task, in a package run by a job) Unfortunately... no logging or failure notification set up BUT, every task/container will only continue to run on SUCCESS and MaximumErrorCount is set to 1. It should have dropped dead if anything went wrong. Here's the overall structure of the package: - read configuration file (set up variables for paths) - load staging tables (amongst other things, this is where the task containing the update stmt is) - do some reporting - a set of queries that appear to have no purpose - send an email notification - export data to the next group -write out to a file -drop staging -FTP said file to the next group -Archive said file The other group acknowledged receiving the file, but somehow we have no archive. AND of course, this all worked when we did dry runs. At this point, grasping at straws. For some reason, there's a copy that works and a copy that doesn't. I don't know what to think anymore. If I do find out what it was, I'll report back. Otherwise, sorry for the wasted space :(