Excel connection manager woes - what are my other options for creating and using saved connection managers

Question

Excel connection manager woes - what are my other options for creating and using saved connection managers

nonghead-webspam

SSCommitted

Points: 1935
More actions
February 3, 2014 at 5:50 pm

#289027

I have some code which will loop through a directory, and operate on all the Excel files in there for a certain file name wild card, i.e. "Weekly*.xlsx"
For this to work, my initial connection manager has to have at least one of the files in there already set up in its connection string, otherwise, it will blow up when the package does some sort of preprocessing error checking before it will run.
Each week, the file names will change. This needs to be automated.
I have other code where I can create a connection in a script task, but that seems to mean that I need to do all the other processing in the script task too.
I would like to, upon entry to the package, look for the existing files, and create a preliminary connection string that will be changed as it loops through the files.
Is there a way to create some sort of data source that does not use a connection manager, and can be created and saved for later use?
OR, is there a way to turn off the preprocessing, so the connection string can be formed and put in place before the package tries to evaluate it?
here's a little bit of script code I'm using now, with the appropriate imports modules:
Dim strFolder = Dts.Variables("IncomingFilePath").Value.ToString
Dim colFiles As String() = Directory.GetFiles(strFolder, "Cco*.xlsx")
'get Ccco file name
For Each strFile In colFiles
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
strFile & ";Extended Properties=""Excel 12.0 XML;HDR=NO"""
vs.Item("User::CccoConnectionString").Value = strConnection
Exit For
Next

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Did you set the DelayValidation property on the connection manager?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 2

I just tried it, and same problem. Didn't know that was there.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 3

I might be making process. I set DelayValidation to True on the whole package, and that gave me a bit more result.

Thanks for the insight.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 4

Got it working. It only finally started working when I set DelayValidation at the topmost level. I also set it at intermediate levels. Topmost makes the most sense.

Thank you much.