0

I have to extract data using a sql query by passing values in where clause. The values to be passed reside in excel. I don't want to hard code those values in query.

Can anyone guide me on how to pass those values.

Eg: Select Name,Age,Address from XYZ where ID in () Now i have to pass the ID's that i have in my excel sheet.

Puzzle SQL
  • 13
  • 2
  • 1
    Do you have access to create tables in the database where you're pulling values from? – Kyle Hale Jan 12 '15 at 17:51
  • For your OLEDB Provider, you'll want to set your Data Access mode to "SQL command from variable". Then you'll need to build out the IN clause based on your Excel data. How you consume that Excel data is *very* dependent on your needs. Perhaps for each value in the spreadsheet, you need to run an extract query and provide output. That'd be a ForEach Enumerator pattern. Maybe you need to get all the IDs from Excel and then pass that as the source. There your logic is going to be consuming and building that list of values. Need more details but I suspect this will be closed as a dupe – billinkc Jan 12 '15 at 17:58
  • Further reading http://stackoverflow.com/a/21242694/181965 http://stackoverflow.com/a/11568119/181965 http://stackoverflow.com/a/7954984/181965 and probably others – billinkc Jan 12 '15 at 18:03

3 Answers3

0

Assuming the excel file is in a location that's always known you can use the Excel Data Source to get to the information. Take a look at:

http://msdn.microsoft.com/en-us/library/ms141683.aspx

It does support SQL like syntax but it will be much easier if the worksheet has two rows - a column header and a data row. You pull the named parameters out of the data row.

Also, my recollection is that you do need to have Microsoft Office installed on the machine executing the SSIS job so keep that in mind as you deploy.

Once you have the values from the Excel Data Source, store them in variables where you can pass them as value parameters to your SQL query. See:

how to store sql query result in a variable and messegeBox

Community
  • 1
  • 1
David Hazlett
  • 111
  • 4
0

Using script task access the excel sheet and populate an SSIS variable(of type object). Then, build an SQL query in a FOR EACH LOOP CONTAINER by iterating over all the values. Store that SQL in another variable(string) and finally execute it using EXECUTE SQL TASK.

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • thanks....i am new to using SSIS. I refereed to the link http://msdn.microsoft.com/en-us/library/ms403358.aspx. I am able to create the script but the package is throwing an error that the excel file is already open by other user when i try to display the result – Puzzle SQL Jan 13 '15 at 16:42
0

You can use a Script Task to iterate all values in the Excel Worksheet and append values in SSIS user variables with each values separated by a comma.

Then you can use Execute SQL Task to execute the SQL with a parameter created in Script Task

Raunak Jhawar
  • 1,541
  • 1
  • 12
  • 21