Running the same piece of code on SSIS and SSMS - but that code is returning different row counts in the two different environments. I am running SSMS 2008 and SSIS on VS 2008. I have verified that I am connecting to the same server and DB in both environments. Anyone encountered/resolved a similar issue before? All help appreciated! Thank you.

Do you mean when you run that (a query?) in BIDS you get one result, and then when you run it SSMS another? It could be your query settings in SSMS, but what do you execute?Arthur My Blog

Yes. Same query in BIDS gives me a different row count (fewer results) than that same query in SSMS (shows accurate row count).

Please post specifics as The query How you use it in BIDS Arthur My Blog

could you tell us how you get row count from the query in SSIS? did you used count(*) function or any other way?http://www.rad.pasfu.com

No, row count in SSIS simply from running a SELECT query statement. I can try inserting into a temp table in SSMS environment and seeing if row count changes.

I meant how you get row count from simple select query? did you used row count transformation on a source and get result? did you used count(*) function and fetched number of rows? where in the SSIS you checked row count of your query?http://www.rad.pasfu.com

Unfortunately, I can't paste the exact code. It's just a select statement. Use the query in SSIS as part of a Data Flow Task (OLE DB Source). Is that what you meant by question #2?

did you tried simple PREVIEW button in the oledb source? and do you mean number of rows there? there just limited number of rows there just for preview.(this is not exact row count)http://www.rad.pasfu.com

Used row count transformation in SSIS.

No, I don't mean number of rows returned in preview. SSIS shows data flow task as complete - but there are fewer number of rows recorded. I also ran a COUNT function against the table that was created through the SSIS package -also showing fewer rows transferred from OLE DB source to OLE DB destination.

Not sure if you've worked this out, but I ran into a similar situation last week and thought my ramblings might help. The basic information of what I was working with is as follows: Source A - one (master), Source B - many (details) Had an order by in both sources to avoid a sort components (set "is sorted" = true). Merge join down further down the pipe. Doing validation my totals(sums of measurable columns) were slightly off...focused on one "Master" record with about 6 corresponding "detail" records, where only 5 were coming through to my destination. Essentially, it looked like the merge join was finishing prior to one of the preceding transformations even completed, but I could have been halucinating. Wound up stripping the order by out of the SQL, and throwing in the sort components (sigh)...everything came back to the expected totals and counts. Was in a rush and burned an hour debugging it...so didn't really look into the "why's" of it.

The "why" for you, DeviantLogic, is likely a collation mismatch between your SQL Server and SSIS... of course, that's almost impossible to prove. It does highlight what Reza is trying to get at though - make sure you're measuring the row count properly in SSIS. Make sure your Row Count is placed in the right spot, and that you don't try to query that variable before the Data Flow has finished. You may also want to enable Package Logging, specifically with the OnPipelineRowsSent event. Talk to me now on

Have you by chance set ignore failures for the data flow transformations? due to which some records have error before the row count transform?