For my data flow task I have a OLEDB Source. In the SQL command section of this I have compiled a select query based on tables from two different databases, held on the same instance. Every time I run this it errors, but when I moved the tables to the same database (for testing purposes) it worked.
I'm guessing from this that the source data needs to be from the same database but is there anyway around this? I tried using a look-up but I couldn't get it to work. I could create a view in the source database but I'm guessing there must be a way to keep it all within the package.
Thank you in advance! This is the query I was using in the OLE DB Source:
select * from commoncomponents.meta.ItemTypeLabelDefinition where internalid not in ( select internalid from iscanimport.dbo.ItemTypeLabelDefinition )
Not sure why the cross-DB query wouldn't work in the one source, but one method would be to create two OleDb Sources, one pointing to CommonComponents DB doing the select from ItemTypeLabelDefinition, and the other pointing to IScanImport and the select statement from your sub-query. Preferably sort these the same way at source in your queries, then use a Merge Join task to combine them.