Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package Updating Records Incorrectly


SSIS Package Updating Records Incorrectly

Author
Message
mrtrich99
mrtrich99
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
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?
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11590
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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
mrtrich99
mrtrich99
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
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?
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11590
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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
mrtrich99
mrtrich99
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 161
No, i'm just using an "Execute SQL Task" and the sql queries are in there.
tmitchelar
tmitchelar
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 508
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.
mrtrich99
mrtrich99
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 161
Yup, you called it. I think I found were the problem lies in the code.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search