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.

Improve this question
5
  • 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 stackoverflow.com/questions/4444169/… Commented Feb 20, 2020 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. Commented Feb 20, 2020 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. Commented Feb 20, 2020 at 21:06
  • If you need more help, billinkc has an excellent answer here: stackoverflow.com/questions/21536719/… Commented Feb 20, 2020 at 21:06
  • Ahah! That does the trick. Commented Feb 20, 2020 at 21:52
Related questions
0
create new column dynamically
12
Dynamically create table in SSIS data flow
0
How to make SSIS choose data source depending on parameter?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.