0

Let me start off by saying that I'm very new to SSIS and have only done very simple data transfer tasks before, so please bear with me.

My task is as follows. I have a SQL server database with a table that stores information about excel files. (Their owner, filepath, status, etc). I want to select all rows from this table that have not yet been processed. Then, for each of those rows, I want to use the filepath in that row as an excel data source and insert data into the DB based on data in the excel files.

I can use an OLE DB Source to get data from the column and then do a conditional split based on the status, but I don't know how to establish a new excel data source for each row after the split.

mrplainswalker
  • 701
  • 4
  • 8
  • 26
  • SSIS doesn't deal with dynamic data transfers very well. It really wants to know all the column names and datatypes/lengths before it executes a data flow. It's possible to use a Script Task to handle the files and sheets dynamically. See here https://stackoverflow.com/questions/4444169/import-data-from-excel-using-ssis-without-knowing-sheet-name – digital.aaron Feb 20 '20 at 19:47
  • All of the column names and datatypes/lengths will be the same. The excel files all have the same format. The only difference is the filepath. – mrplainswalker Feb 20 '20 at 20:56
  • In that case you should be able to create the source connection manager statically for a single Excel file. Once you have that working, you'll add in an Execute SQL Task to grab the records from the SQL Server table and store them in an ADO object for use in a Foreach Loop. Iterate through the table of file paths to build a URI string, then use THAT string in an Expression on the connection manager to set the `ExcelFilePath`. – digital.aaron Feb 20 '20 at 21:06
  • If you need more help, billinkc has an excellent answer here: https://stackoverflow.com/questions/21536719/dynamically-assign-filename-to-excel-connection-string – digital.aaron Feb 20 '20 at 21:06
  • Ahah! That does the trick. – mrplainswalker Feb 20 '20 at 21:52

0 Answers0