Wildcard in Excel Connection String

Question

Wildcard in Excel Connection String

KT-243628

SSC Veteran

Points: 254
More actions
September 15, 2006 at 2:26 am

#115727

Please help...!
I have a set of spreadsheets I want to extract into my database. The users will be updating these spreadsheets and renaming them slightly when they do so with a version number. I therefore don't want to point directly at the file, rather use the first part of the name with a wildcard. I have tried doing this firstly through changing the connection string in the expressions, and then by setting a variable with the wildcard and using this in the connection string. I have got the latter method working with flat files, but it just doesn't seem to work with spreadsheets! I cannot find anything on the msdn site and wonder if its actually possible.
The error I get is:
The AcquireConnection method call to the connection manager failed with error code 0xc02020009
An OLEDB error has occured etc etc
TIA
KT
September 18, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Fattman Old Hand Points: 330 More actions · Answer 1

Hi,

The Excel connection is a bit lumpy - it uses the Jet db engine which is not the most modern & seems to get upset by the slightest thing.

I have solved something similar by using a Script step to copy one file at a time from a 'Holding' folder into a 'Load' folder, renaming it to a consistent name each time e.g. ExcelSource.xls. I can then have a simple connection which points at this file in this folder and, as long as the structure of the file remains consistent, you don't have to mess with the connection itself. At the end of processing the file you can archive the original (or however you want to maintain things in your process).

You can use variables to maintain the filepaths and to keep track of the individual version (if it's not contained in the file itself, and indeed if you care).

This can also work to provide a way to loop to keep processing files in the Holding folder, providing a kind of drop-box for users to just plop their workbooks into, perhaps using a recurring SQLAgent job to execute the SSIS package.

Matt

KT-243628 SSC Veteran Points: 254 More actions · Answer 2

Hi Matt

Thanks for the response, and the suggestion, I must admit I had not thought of this but this sounds like a good solution. In the interim however, I have convinced the user they want to keep the spreadsheet name static and keep the versioning information within the spreadsheet! I will remember this one in the future though.

Thanks again

KT

Fattman Old Hand Points: 330 More actions · Answer 3

Hey, KT,

No worries - sounds like you solved this in a way which is far superior to a technical solution - change the requirements!

Matt