Ssis Connection Error - File Name Not Valid
Solution 1:
Based on the information presented, you are doing everything correct. If you're new to SSIS, one thing I'd suggest, is that you get a copy of the excellent add-in BIDSHelper. It has great features that can really save you time especially with regard to configurations and expressions.
I created a reference package that had an Excel Connection Manager pointing to C:\ssisdata\so_paulsmithjr.xls and wired everything up.
At this point, I know things are working so it was time to make the package move. I created the following variables and their values
- CurrentFile - C:\ssisdata\so_paulsmithjr.xls
- PlaceHolder - ##FILE_PATH##
- TemplateConnection -
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=##FILE_PATH##;Extended Properties="Excel 8.0;HDR=YES";
A fourth variable is set to be an expression (Right click on variable, properties window. Set Evaluate as Expression = True & Expression is below)
- CurrentConnection -
REPLACE(@[User::TemplateConnection], @[User::PlaceHolder], @[User::CurrentFile])
I compared the CurrentConnection value to the ReferenceConnection (which is the original value of the Excel Connection Manager's connection string) and things were a match. At this point, if I were to change the value of CurrentFile to C:\ssisdata\so_paulsmithjr - Copy.xls, that would automatically be reflected in the value of CurrentConnection.
The final trick would be to use an Expression on the Excel Connection Manager. Again, right click on the CM and under Properties, there will be Expressions. It won't expand as there is nothing under it. Instead click the ellipses and then select ConnectionString property and select the ellipses again and this time drag down the @[User::CurrentFile] variable. Click OK x2 and now your connection manager is set to use wherever the CurrentConnection variable specifies.
Does that work any better?
Post a Comment for "Ssis Connection Error - File Name Not Valid"