Using a variable in Excel Connection Manager

Question

Using a variable in Excel Connection Manager

scotdg

Right there with Babe

Points: 771
More actions
August 8, 2019 at 7:54 pm

#3670035

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

palandri Old Hand Points: 387 More actions · Answer 2

Try setting a breakpoint just after the SQL task and verify the variable values returned by the task are what you expect.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

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.

scotdg Right there with Babe Points: 771 More actions · Answer 4

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