Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package Updating Records Incorrectly Expand / Collapse
Author
Message
Posted Friday, October 29, 2010 3:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, Visits: 161
Hi,

I have an SSIS package that has an Execute SQL Task. In this task I have a SQL update, when I run the package the first time the update works fine but when I run it a second time it updates everything that wasn't supposed to be updated. Nothing changed in the database it's just every time after the first run the update doesn't work properly.

I then tested the SQL code to see if there is something wrong with it by running the package once and then copying and pasting the SQL update code in to SQL management studio to see if I get any records that would update and I get 0. I also tried executing the task just by itself, without running the whole package, and it to executed without any additional updates.

I have a date parameter in the code which I commented out because maybe that was what was causing the incorrect updates but it's not. I would post my code but I know it's not the code that's an issue due to the 2 tests I performed to validate if the SQL was working properly so my question is has any experienced this type of behavior in executing an SSIS package?
Post #1013363
Posted Friday, October 29, 2010 3:55 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 1,452, Visits: 8,252
mrtrich99 (10/29/2010)
Hi,

I have an SSIS package that has an Execute SQL Task. In this task I have a SQL update, when I run the package the first time the update works fine but when I run it a second time it updates everything that wasn't supposed to be updated. Nothing changed in the database it's just every time after the first run the update doesn't work properly.

I then tested the SQL code to see if there is something wrong with it by running the package once and then copying and pasting the SQL update code in to SQL management studio to see if I get any records that would update and I get 0. I also tried executing the task just by itself, without running the whole package, and it to executed without any additional updates.

I have a date parameter in the code which I commented out because maybe that was what was causing the incorrect updates but it's not. I would post my code but I know it's not the code that's an issue due to the 2 tests I performed to validate if the SQL was working properly so my question is has any experienced this type of behavior in executing an SSIS package?


Are you passing a parameter (from outside the execute SQL Task) to the SQL logic?

If so, is that parameter used in a WHERE clause? If so, double check that you're passing the right parameter. if required, run profiler to capture the update statement that being executed.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1013369
Posted Monday, November 1, 2010 6:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, Visits: 161
I think i found the root of the problem. The package i have is inserting records into the table where the update needs to happen prior to the update. So here's the high level.

Insert records in table A
Update records in table A based on criteria

Now if I want to re-run the package it's supposed to not insert the records that have already been inserted in table A.

The problem i'm seeing is that the SSIS is not catching those records and inserting the records that have previously been inserted in table A causing my update query to update all these records because the update is to catch duplicates.

The fields i'm joining on to see if the record exists are
1) Money field
2) Bit field
3) Varchar field

Now I took the query that checks to see if the record exists and ran it in management studio and it didn't insert it but when the package runs it inserts those records previously inserted in the table.

Does anyone know why this is happening? Why would a query run properly in management studio but when put in an Execute SQL Task it runs differently?
Post #1014223
Posted Tuesday, November 2, 2010 7:36 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 1,452, Visits: 8,252
Are you doing this in the data flow?

I've had issues before with the data flow and records not being committed when I expected them to be.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1014469
Posted Tuesday, November 2, 2010 9:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, Visits: 161
No, i'm just using an "Execute SQL Task" and the sql queries are in there.
Post #1014636
Posted Wednesday, November 3, 2010 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
It sounds to me like it is a problem with your lookup(or Execute SQL Task that is used to insert records into your table) and not your update statement. Hard to say much else without more details, but I would guess that your join isn't correct or working how you assume it will.
Post #1015350
Posted Wednesday, November 3, 2010 8:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 5:00 PM
Points: 50, Visits: 161
Yup, you called it. I think I found were the problem lies in the code.
Post #1015663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse