Using a lookup as an inner join across instances in SSIS
I recently had a need to pull data from two different SQL instances and use one instance as the validation for a second one. I was creating an inner join between SQL instances. No linked tables or open query. Depending on your environment you'll need an SSIS Service account that has execute (or read) to both instances so you can get the data back and the ability to use the SSIS service account to write a file somewhere.
With that aside I'll do a step but step way to create a basic package that queries two sql instances and uses a lookup to give you the inner join effect and writes a file.
I'm using Visual Studio Community 2015 (If you don't have dev essentials yet go get it) and SQL 2014 Developer edition for this guide.
Step 1: Launch Visual Studio then click file>new> project. Choose integration services as below
Now drag a data flow task onto the control flow
Next go to the Data Flow Tab and add the first OLEDB Source. This the the data main source not the comparison.
Now you can add the comparison source OLEDB. You don't connect it to the lookup as the data flowing through is just going to use it as a filter not as an additive. There's a different transform for that type operation.
Now set up the lookup transform
Next step make the equi/ inner join criteria in the columns section
Lastly set up the flat file output. I prefer the CSV output over excel as the overwrite capacity is built into the transform.
Go back to the Control flow and execute the package by right clicking on it and choose execute.
Now confirm that it all executed as expected by checking the data flow for green check marks.