Dynamically assign filename to excel connection string

by

Option A

The ConnectionString property for an Excel Connection Manager is not where I go to manipulate the current file, which is contrast to an ordinary Flat File Connection Manager.

Instead, put an expression on the Excel Connection Manager’s ExcelFilePath property.

enter image description here

In theory, there should be no difference between ConnectionString and ExcelFilePath except that you will have more “stuff” to build out to get the connection string just right.

Also, be sure you’re executing the package in 32 bit mode.

Option B

An alternative that you might be running into is that the design-time value for the Connection String isn’t valid once it’s running. When the package begins, it verifies that all of the expected resources are available and if they aren’t, it fails fast rather than dieing mid load. You can delay this validation until such time as SSIS has to actually access the resource and you do this by setting the DelayValidation property to True. This property exists on everything in SSIS but I would start with setting it on the Excel Connection Manager first. If that still throws the Package Validation Error, try setting the Data Flow’s delay validation to true as well.

More Related Contents:

  1. How do I pass value to a stored procedure parameter in OLE DB Source component?
  2. Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
  3. SSIS reading LF as terminator when its set as CRLF
  4. Importing excel files having variable headers
  5. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  6. LocalDB deployment on client PC
  7. How to check if a column exists in a SQL Server table
  8. Auto increment primary key in SQL Server Management Studio 2012
  9. XML Server XML performance optimization
  10. Import Excel spreadsheet columns into SQL Server database
  11. convert Excel Date Serial Number to Regular Date
  12. Adding a query hint when calling Table-Valued Function
  13. SSIS Excel Import Forcing Incorrect Column Type
  14. Why the SQL Server ignore the empty space at the end automatically?
  15. Automate Version number Retrieval from .Dtsx files
  16. Query a database based on result of query from another database
  17. Running an SSIS Package using dtexec
  18. SSIS Source Format Implicit Conversion for Datetime
  19. Last executed queries for a specific database
  20. How to Map Input and Output Columns dynamically in SSIS?
  21. Avg of float inconsistency
  22. SQL Server Configuration Manager cannot be found
  23. Reverse engineering SSIS package using C#
  24. Joining multiple tables returns NULL value
  25. Temporarily disable all foreign key constraints
  26. Is it possible to use user defined aggregates (clr) with window functions (over)?
  27. “No backupset selected to be restored” SQL Server 2012
  28. What are the differences between Merge Join and Lookup transformations in SSIS?
  29. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  30. How to avoid SSIS FTP task from failing when there are no files to download?

Leave a Comment