How Can You Use JOIN Statement From Excel Source Data To SQL Server Data For New Data Set For Lookup In Data Flow Task of SSIS

Question

I have an Excel Source that connected to a Lookup Transformation that is then connected to an OLE DB Command that holds a stored procedure to Insert my data into SQL Server.

The following is the query I want within the Data Flow before the Lookup Transformation:

SELECT * 
FROM EFF f
LEFT OUTER JOIN OH o  ON f.No = o.Name
LEFT OUTER JOIN Ay a  ON f.No = A.Reg
INNER JOIN      Oh ot ON a.Num = ot.name
WHERE f.Type LIKE  'z%' 
 AND  o.is null

So basically, I want to use this query to do a Lookup on using the Excel Source data...would I use a Script Component?

Are we to assume EFF, OH and AY are worksheets in Excel or something? How does this differ from your source? — billinkc, Mar 11 '15 at 02:30
@JohnSaunders The `Lookup` won't work because there was already a lot of data in the database. There was a stored procedure similar to above but instead of `SELECT *` it was `UPDATE f SET f.no = a.Num FROM...` Because this data was rectified outside of the ETL Process, I need to use the SELECT statement in my question to run on the Excel Source data in my Data Flow Task for the `Lookup` — J.S. Orris, Mar 11 '15 at 18:10
@billinkc Please see response to JohnSaunders..There was a bunch of data that was rectified outside the ETL Process...I need to run a Join from the Excel Source data to find the rectified data to Lookup on so that no duplicated data (with unrectified data) is inserted into DB. That's Why I need to get the results of this SELECT query before it hits the Lookup — J.S. Orris, Mar 11 '15 at 18:14
@JeffOrris But I still don't know what EFF, OH and AY are. If they're tables, then you can do all of that in a query on the Lookup Transform. If they're worksheets in excel, then you'd need to use a Cache Connection Manager for your lookup and use merge joins x3 to populate the CCM — billinkc, Mar 11 '15 at 18:24
@billinkc EFF would be the Excel Worksheets. OH and AY are tables in SQL Server...sorry for not clarifying that — J.S. Orris, Mar 11 '15 at 18:26

score 0 · Answer 1 · answered Mar 11 '15 at 00:32

0

I would use SSIS to load the Excel data into a SQL Server table and add the resulting table to the query. I assume the main data source is SQL Server.

answered Mar 11 '15 at 00:32

benjamin moskovits

5,261
1
12
22

I need to run the SELECT statement in my question on the Excel data in my DataFlow before it hits the Lookup – J.S. Orris Mar 11 '15 at 18:11
1) Load the data from Excel into SQL Server. 2) Run the select statement 3) Do the lookup. What am I missing? – benjamin moskovits Mar 11 '15 at 18:14
The excel data needs to be joined to SQL Server tables so get result set needed for Lookup – J.S. Orris Mar 11 '15 at 18:27
Edited question title – J.S. Orris Mar 11 '15 at 18:30

How Can You Use JOIN Statement From Excel Source Data To SQL Server Data For New Data Set For Lookup In Data Flow Task of SSIS

1 Answers1