5

The name of the file changes based on months. Every month you have a new file. 

I:\Test\Data_201303.xlsx

How do I set up a connection manager that will work with variable file paths?

JJ.
  • 9,580
  • 37
  • 116
  • 189

2 Answers2

10

Look for the "expression" property on the connection manager. That's where you set it to USER::VariableName.

More detail: if you "edit" the connection manager it will show you the edit window. That's not the one you want. Look at the "properties" window, which is where you will find the "Expressions" entry, in front of an empty box.

Click on the empty box, and it will show you a button with three dots on it. Click on that button. This pulls up the "Property Expression Editor". There is a dropdown with properties like "ConnectionString" on the left. On the right, there is ANOTHER button with three dots. Click on that button to pull up the "Expression Builder". If you have declared any variables, you will find them listed in the upper left hand corner, e.g., as User::VariableName, and you can then drag them into the Expression box, where they will appear as @[User::VariableName].

Not obvious, but doable.

criticalfix
  • 2,870
  • 1
  • 19
  • 32
  • 2
    ConnectionString or ExcelFilePath property? – JJ. Mar 05 '13 at 18:45
  • 2
    ExcelFilePath, presumably. For other flatfile connection managers it usually seems to be the ConnectionString, but I haven't used the Excel one much. – criticalfix Mar 05 '13 at 19:00
  • TITLE: Microsoft Visual Studio ------------------------------ Error at From Excel to ReviewsBucket [Excel Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error at From Excel to ReviewsBucket [Excel Source [1]]: Opening a rowset for "I:\Test\Files - Temp Location\Reviews201303.xlsx" failed. Check that the object exists in the database. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap) – JJ. Mar 05 '13 at 19:01
  • That looks like an OLE DB error. Did you wire the variable into the Excel connection manager? It might have gotten wired into a database connection manager instead. That wouldn't work. – criticalfix Mar 05 '13 at 19:04
  • Do you know why you're getting a database error when trying to open an Excel file? – criticalfix Mar 05 '13 at 19:19
  • No clue. It isn't linked to a db by any means that's whats so strange about this. – JJ. Mar 05 '13 at 20:03
  • So, it works when you hardwire I:\Test\Data_201303.xlsx, but it fails when you wire an expression to a variable that contains I:\Test\Files - Temp Location\Reviews201303.xlsx? What if you put I:\Test\Data_201303.xlsx into the variable? Is it just the path? – criticalfix Mar 05 '13 at 20:13
  • @criticalfix - can you assist I can not find the expression property for love nor money! I right click the connection / then properties then have nothing on the screen that says 'expression' with three dots next to it - see here - http://bit.ly/11ICEUo - any assistance really appreciated!! – megaSteve4 Dec 01 '14 at 13:07
  • @megaSteve4 - I see you're using VS2013, so you're ahead of me. It looks like your Properties window is showing just "File Name" and "Full Path", where I would expect to see an "Expressions" option as well. Is this just the Excel Connection Manager? Do you see an "Expressions" option for any of the other connection managers? – criticalfix Jan 19 '15 at 17:26
6

You need to set the expression for the ServerName or ExcelFilePath property to modify ConnectionString of Excel connection manager dynamically using an SSIS package variable.

Here are some SO answers that deal with looping multiple Excel files :

How to loop through Excel files and load them into a database using SSIS package?

How to import Excel files with different names and same schema into database?

Community
  • 1
  • 1
  • I tried the same method but I am receiving an error: http://stackoverflow.com/questions/23158021/how-to-set-the-connection-string-in-expression-for-excel-in-ssis-package – Si8 Apr 18 '14 at 16:30
  • @user756519 - can you assist I can not find the expression property for love nor money! I right click the connection / then properties then have nothing on the screen that says 'expression' with three dots next to it - see here - http://bit.ly/11ICEUo - any assistance really appreciated!! – megaSteve4 Dec 01 '14 at 13:13