October 29, 2010 at 3:43 pm
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?
October 29, 2010 at 3:55 pm
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.
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[/url]
November 1, 2010 at 6:33 pm
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?
November 2, 2010 at 7:36 am
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.
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[/url]
November 2, 2010 at 9:50 am
No, i'm just using an "Execute SQL Task" and the sql queries are in there.
November 3, 2010 at 9:35 am
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.
November 3, 2010 at 8:22 pm
Yup, you called it. I think I found were the problem lies in the code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply