CODEWITHSUNDEEP
sql-serverssis
user1178399
user1178399

Reputation: 1128

SSIS - read data from sheet and pass each row to a stored procedure

I need to read data from excel sheet and pass each row as parameters to stored procedure. i added Ole db Source to get data from Excel. but what then?

Any ideas on how to do it?

Upvotes: 1

Views: 1961

Answers (1)

Ross McNab
Ross McNab

Reputation: 11587

Add a connection manager for the SQL database that contains the stored procedure.

Feed the resultset from the Excel OleDB Data Source into an OleDB Command, and in the editor for the command:

  • Connection Managers tab - choose your new SQL connection manager
  • Component Properties tab - set "SQLCommand" to EXEC your_stored_proc ?, ?, ? with a question mark for each parameter you want to pass to the stored procedure.
  • Column Mappings tab - map your input fields onto the ? placeholders.

Upvotes: 4

Related Questions