I am using SSIS under 2008 R2 RTM and have an OLE DB connection with a query from the database that I need to join to two other dimension tables to lookup the right codes. I am using two lookups to do this and then inserting into the fact table with a Ole db destination task but when I run the data flow task, there are no errors but no data is inserted into the database. No data is being selected on the lookup no match output (I put in a flat file destination to see if any data was coming in this way but it is not). If I write the ole db source and lookup code in T-SQL with inline subselects, I do get data so I am a bit stumped (doesn't seem like a trailing space issue). Any ideas what I could be doing wrong?
Update: only other clude is that I get a warning "Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available". Tried just using a oledb source and destination component with T-sql that should work to select data but still not saving any data. Transaction set to supported on the package.
So you are getting zero rows on your no match output. How many rows are you getting downstream of the match output going to the OLEDB Destination? Have you put a data viewer anywhere?
I have a data viewer after the OLE DB Source which does not do anything when I double click on it at runtime. All tasks are green and the lookups are caching (full cache) rows but the OLE DB destination shows 0 rows inserted on the progress tab. Could it be too much data for the lookups to handle? Should be about 20,000 records from the OLE DB source for the fact table, and the first lookup has 34,000 rows and the second lookup has 15,000 rows.
Ok, just figured this out. The issue was not having a set nocount on statement which returned the number of rows selected and not the data: http://www.bidn.com/blogs/dustinryan/ssis/835/ssis-ole-db-source-using-table-variables-returns-no-rows.