I am using Execute SQL Tasks to run stored procedures which do the following: (1) compare the staging table to the destination table and update a slowly changing dimension in the destination; (2) delete rows from the staging table that are identical except for some derived columns (added during the ETL that populates the staging table). After these execute, the ETL flows the remaining rows from the staging table into the destintation. My problem is that the SQL doesn't work correctly within SSIS: most of the SCD's do not get updated and most of the redundant rows do not get deleted. However, if I use exactly the same SQL to test for redundant rows in the destination table after the fact (or to delete those rows with a script), the SQL works. It seems that the redundant values I am comparing in the WHERE clause do not match when they are in different tables, but they do match when they are in the same table. I have confirmed that the two tables have identical structure. Could SSIS be attaching some kind of invisible baggage that prevents this method from succeeding?

My problem is that the SQL doesn't work correctly within SSIS: most of the SCD's do not get updated and most of the redundant rows do not get deleted. However, if I use exactly the same SQL to test for redundant rows in the destination table after the fact (or to delete those rows with a script), the SQL works. If you are using Execute SQL Tasks to execute stored procedures then SQL Server is processing the statements not SSIS. All SSIS is doing is asking SQL Server to execute the procs. There must be something else causing the problem. Could you please give an example of the WHERE clause that is not working? Are the staging and destination tables in the same database? Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

maybe you run execute sql task parallel with other task(s) who import some data into tables which you used to query with in execute sql task. did you checked it?http://www.rad.pasfu.com

Hello, IF Query works in SSMS , it will work exactly same in SSIS package. You might be passing some parameter values to your query in Execute SQL task, those are not correct , this can cause un expected results. OR You are building your query in some variable and using that in Execute SQL Task and while building your query you have passing some values those are not passed correctly in package that caused un expected results. use exactly the same query in SSMS and then in SSIS and see if still you are getting different results? Thankshttp://sqlage.blogspot.com/

I would also recommend using SQL Profiler to capture the commands that the server is receiving. It should be very obvious when looking at the captured statements to determine what the difference is. Talk to me now on

Thanks, Jeff. I was thinking along the lines of something happening to the data definitions in the pipeline between the Flat File Source and the staging table, and/or between the staging table and the target table, that makes one or more field/value comparisons fail. The table definitions are identical to my eye: CREATE TABLE [dbo].[STG_WIPActiveJobs]( [RID] [int] IDENTITY(1,1) NOT NULL, [LoadDate] [datetime] NULL, [FileDate] [datetime] NULL, [AsofDate] [datetime] NULL, [br] [nvarchar](50) NULL, [d] [nvarchar](50) NULL, [typ] [nvarchar](50) NULL, [id] [nvarchar](50) NULL, [st] [nvarchar](50) NULL, [chrg] [nvarchar](50) NULL, [chrgno] [nvarchar](50) NULL, [MakeID] [int] NULL, [model] [nvarchar](50) NULL, [ParentModel] [nvarchar](50) NULL, [ser] [nvarchar](50) NULL, [open] [nvarchar](50) NULL, [dtlastact] [nvarchar](50) NULL, [dtcls] [nvarchar](50) NULL, [actcust] [nvarchar](50) NULL, [actno] [nvarchar](50) NULL, [cstordr] [nvarchar](50) NULL, [jobnarrative] [nvarchar](50) NULL, [partno] [nvarchar](50) NULL, [description] [nvarchar](50) NULL, [component] [nvarchar](50) NULL, [activity] [nvarchar](50) NULL, [comment1] [nvarchar](50) NULL, [comment2] [nvarchar](50) NULL, [labhrs] [nvarchar](50) NULL, [labcost] [nvarchar](50) NULL, [labsell] [nvarchar](50) NULL, [labest] [nvarchar](50) NULL, [labqte] [nvarchar](50) NULL, [partcost] [nvarchar](50) NULL, [ptssell] [nvarchar](50) NULL, [ptsest] [nvarchar](50) NULL, [ptsqte] [nvarchar](50) NULL, [subcost] [nvarchar](50) NULL, [subsell] [nvarchar](50) NULL, [subest] [nvarchar](50) NULL, [subqte] [nvarchar](50) NULL, [othcost] [nvarchar](50) NULL, [othsell] [nvarchar](50) NULL, [othest] [nvarchar](50) NULL, [othqte] [nvarchar](50) NULL, [reqndue] [nvarchar](50) NULL, [esthr] [nvarchar](50) NULL, [estd] [nvarchar](50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[WIPActiveJobs]( [RID] [int] IDENTITY(1,1) NOT NULL, [LoadDate] [datetime] NULL, [FileDate] [datetime] NULL, [AsofDate] [datetime] NULL, [br] [nvarchar](50) NULL, [d] [nvarchar](50) NULL, [typ] [nvarchar](50) NULL, [id] [nvarchar](50) NULL, [st] [nvarchar](50) NULL, [chrg] [nvarchar](50) NULL, [chrgno] [nvarchar](50) NULL, [MakeID] [int] NULL, [model] [nvarchar](50) NULL, [ParentModel] [nvarchar](50) NULL, [ser] [nvarchar](50) NULL, [open] [nvarchar](50) NULL, [dtlastact] [nvarchar](50) NULL, [dtcls] [nvarchar](50) NULL, [actcust] [nvarchar](50) NULL, [actno] [nvarchar](50) NULL, [cstordr] [nvarchar](50) NULL, [jobnarrative] [nvarchar](50) NULL, [partno] [nvarchar](50) NULL, [description] [nvarchar](50) NULL, [component] [nvarchar](50) NULL, [activity] [nvarchar](50) NULL, [comment1] [nvarchar](50) NULL, [comment2] [nvarchar](50) NULL, [labhrs] [nvarchar](50) NULL, [labcost] [nvarchar](50) NULL, [labsell] [nvarchar](50) NULL, [labest] [nvarchar](50) NULL, [labqte] [nvarchar](50) NULL, [partcost] [nvarchar](50) NULL, [ptssell] [nvarchar](50) NULL, [ptsest] [nvarchar](50) NULL, [ptsqte] [nvarchar](50) NULL, [subcost] [nvarchar](50) NULL, [subsell] [nvarchar](50) NULL, [subest] [nvarchar](50) NULL, [subqte] [nvarchar](50) NULL, [othcost] [nvarchar](50) NULL, [othsell] [nvarchar](50) NULL, [othest] [nvarchar](50) NULL, [othqte] [nvarchar](50) NULL, [reqndue] [nvarchar](50) NULL, [esthr] [nvarchar](50) NULL, [estd] [nvarchar](50) NULL ) ON [PRIMARY] The staging table holds a daily snapshot, which goes into the target table after updating the [AsofDate] in the target and removing records with unchanged 'job states' from the staging table. The stored procedure that updates the [AsofDate] column in the target table (by matching unchanged job states in the staging table) is: CREATE PROCEDURE [dbo].[uspUpdateOpen_WIPActiveJobs] AS BEGIN SET NOCOUNT ON; UPDATE WIPActiveJobs SET [AsofDate] = STG_WIPActiveJobs.FileDate FROM STG_WIPActiveJobs WHERE WIPActiveJobs.br = STG_WIPActiveJobs.br AND WIPActiveJobs.d = STG_WIPActiveJobs.d AND WIPActiveJobs.typ = STG_WIPActiveJobs.typ AND WIPActiveJobs.[id] = STG_WIPActiveJobs.[id] AND WIPActiveJobs.st = STG_WIPActiveJobs.st AND WIPActiveJobs.chrg = STG_WIPActiveJobs.chrg AND WIPActiveJobs.chrgno = STG_WIPActiveJobs.chrgno AND WIPActiveJobs.model = STG_WIPActiveJobs.model AND WIPActiveJobs.ser = STG_WIPActiveJobs.ser AND WIPActiveJobs.[open] = STG_WIPActiveJobs.[open] AND WIPActiveJobs.dtlastact = STG_WIPActiveJobs.dtlastact AND WIPActiveJobs.dtcls = STG_WIPActiveJobs.dtcls AND WIPActiveJobs.actcust = STG_WIPActiveJobs.actcust AND WIPActiveJobs.actno = STG_WIPActiveJobs.actno AND WIPActiveJobs.cstordr = STG_WIPActiveJobs.cstordr AND WIPActiveJobs.jobnarrative = STG_WIPActiveJobs.jobnarrative AND WIPActiveJobs.partno = STG_WIPActiveJobs.partno AND WIPActiveJobs.[description] = STG_WIPActiveJobs.[description] AND WIPActiveJobs.component = STG_WIPActiveJobs.component AND WIPActiveJobs.activity = STG_WIPActiveJobs.activity AND WIPActiveJobs.comment1 = STG_WIPActiveJobs.comment1 AND WIPActiveJobs.comment2 = STG_WIPActiveJobs.comment2 AND WIPActiveJobs.labhrs = STG_WIPActiveJobs.labhrs AND WIPActiveJobs.labcost = STG_WIPActiveJobs.labcost AND WIPActiveJobs.labsell = STG_WIPActiveJobs.labsell AND WIPActiveJobs.labest = STG_WIPActiveJobs.labest AND WIPActiveJobs.labqte = STG_WIPActiveJobs.labqte AND WIPActiveJobs.partcost = STG_WIPActiveJobs.partcost AND WIPActiveJobs.ptssell = STG_WIPActiveJobs.ptssell AND WIPActiveJobs.ptsest = STG_WIPActiveJobs.ptsest AND WIPActiveJobs.ptsqte = STG_WIPActiveJobs.ptsqte AND WIPActiveJobs.subcost = STG_WIPActiveJobs.subcost AND WIPActiveJobs.subsell = STG_WIPActiveJobs.subsell AND WIPActiveJobs.subest = STG_WIPActiveJobs.subest AND WIPActiveJobs.subqte = STG_WIPActiveJobs.subqte AND WIPActiveJobs.othcost = STG_WIPActiveJobs.othcost AND WIPActiveJobs.othsell = STG_WIPActiveJobs.othsell AND WIPActiveJobs.othest = STG_WIPActiveJobs.othest AND WIPActiveJobs.othqte = STG_WIPActiveJobs.othqte AND WIPActiveJobs.reqndue = STG_WIPActiveJobs.reqndue AND WIPActiveJobs.esthr = STG_WIPActiveJobs.esthr AND WIPActiveJobs.estd = STG_WIPActiveJobs.estd; END I deliberately excluded all derived columns (the 1st 4) from the comparison, as I am only interested in identifying unchanged source data at this stage. I just did a trial load of 2 daily snapshots into initially empty tables. I paused after each day and pasted the data into Excel. When I deleted the 1st 4 (derived) columns from the spreadsheet and executed Data > Remove Duplicates, Excel found 5,367 duplicate rows. However, when I ran the above SP, SQL Server found only 575 matches between the 2 snapshots (when the data were still in different tables: Day 1 in the target and Day 2 in the staging). This is the 1st thing that is driving me insane.

Thanks, Reza. No, they are sequential tasks in the same Sequence Container.

Thanks Aamir, Good thought, but there are no parameters or variables involved.

Thanks, Todd. That's beyond my current skill set, but I'll see what I can learn about the tool.

Is it possible that white space is being added to the beggining or end of your string values as SQL Server treats 'MyData' as different then 'MyData ' and ' MyData'. I believe Excel is smart enough to ignore white space before and after text. There could also be special characters (tab, carriage return, line feed) in the strings being matched. The following article explains how SQL Server deals with white space and special characters http://www.sqlservercentral.com/articles/T-SQL/63953/ It could also be a date formatting issue i.e. 12th January 1970 could be formated as 12/01/1970 or 01/12/1970 so depending on the date format settings is Excel and SQL Server, the two dates may not match. SQL Server treats 01/12/1970 as 12th January 1970 but Excel might treat it as 1st December 1970. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

I think my colleague just found it. Several of the fields can acquire NULL values from the Flat File Source (two commas in a row result in a NULL in SSIS, not an empty string). I forgot that NULL <> NULL in SQL. Excel converted these to the string 'NULL', so it saw them as equal. Now, do I send myself back to the [rookie] table or to the [shame] table?

I think my colleague just found it. Several of the fields can acquire NULL values from the Flat File Source (two commas in a row result in a NULL in SSIS, not an empty string). I forgot that NULL <> NULL in SQL. Excel converted these to the string 'NULL', so it saw them as equal. Now, do I send myself back to the [rookie] table or to the [shame] table? I forgot about that as well :-) Glad you found the solutionJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD