Using a variable in Excel Connection Manager
-
I am trying to create a package that will import a file using the filename from a SQL table. I first created the package to read a single filename and it worked fine. Now I am trying to add a SQL task before the Data Flow that get the filename from the table. I set the result set to Single Row. Gave the result set a name and selected my variable name (ImportFile). In addition to ImportFile I have two other variables which are FilePath and FullPath (FilePath + ImportFile). On the Excel connection manager I set the ExcelFilePath to FullPath and changed DelayValidation = True. When I run the package I get an error that it cannot acquire a connection from connection manager.
Any Suggestions are greatly appreciated.
-
Run the package in debug mode to determine exactly when the error is generated. Also to be 100% certain that the connection string being assembled is in the right form.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Try setting a breakpoint just after the SQL task and verify the variable values returned by the task are what you expect.
-
Also try to set the DelayValidation property on the DataFlow task itself.
A safe approach I usually follow in these scenarios is to create an empty template file with the same layout, and use that as the default value in the connection manager.
-
Thank you. That is exactly what I needed to do. I created a template file and used that to set the connection to the excel file/sheet. I had tried this previously without success so I think it was the combination of this and setting the DelayValidation property.
Thanks again to everyone that took the time to respond.
Scott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply