How to pass excel file name using a variable
-
I have a data flow task where it reads the data from excel sheet and loads it to sql server. When i hard code source Excel FilePath(with a specific name) in the connection Manager it works fine...but now i need to make sure that Excel OLE DB source picks up any file in the folder(irrespective of the filename)....I know i can use Expressions(for the Excel Connection Manager) and pass the ExcelFilePath(along with File path and file name) but don't know how to do it..what value should i pass for the filename(to pick up any file with .xls extension)
Thanks in advance
-
Hi Sandy
You may try the following approach; create a package level variable, its name could be User::FileName; this is the variable that will contain your dynamic excel file, you may initialize it from a config file or as a package parameter or by any other mean, make sure its initial value is a valid Excel file location; then follow these steps:
1. Click in your package's Data Flow containing the Excel Source component (as shown by the picture below)
2. Right-click on the Excel Connection Manager located on the connections manager panel, and select properties.
3. You will see the Connection String for your Excel Connection Manager (this is a verification step, nothing to do here)
4. At the Expressions properties, click at the 3 dots button, you will get a pop-up windows where you can map properties with expressions, at its first row, click on the properties' drop-down and select ServerName then assign the variable User::FileName as its expression.
This should do it, give it a try and let us know how it goes.
Cheers,
Hope this helps,
Rock from VbCity -
Thanks...I tried what u said but when i preview its giving a message Microsoft Jet Database Engine "Invalid Argument"
also just FYI i m trying to save the excel filepath and name(some dummy name) in config file.
I noticed something called Excel File in the variables..Can we use that??..I was actually trying to use that before, but it was failing
-
Ok, let's suggest some debugging:
a. Your Excel file location is a package's parameter; Does it have spaces? All my tests involved file names without 'spaces' and I always avoid them.
b. Would it be possible to test the package logic using a package level variable, assigning the Excel file full path? I will suggest something simple C:\Temp\MyTest.xls
c. Would you be able to add an Script Task on the Package Control Flow, prior to the Excel File data source (which is at the package's data flow)? the script tasks will take the variable containing the Excel file location as a read-only parameter and you will show its value using a MessageBox statement.
d. I ran out of ideas, was thinking of a network drive or your Excel file having a password, but let's wait for your reply to plan the next action.
Would you be able to post the full name of the Excel file you are trying to process, including its path?
Cheers,
Hope this helps,
Rock from VbCity -
I am 100 % sure it's getting the right file name.....Now it looks like a different problem
our client submit an excel file every week and it was processing fine until a couple of months ago...If i run the old excel file it works fine and inserts the data into the database...Now a different person is submitting the excel file, it doesn't load data into the database but if i copy the new data(coming from the new excel file) inside an old excel file(which we used to get before from a differnt person) it works fine and all the data is inserted into the database..
When i try to preview the Excel OLE DB source i get the following error message
"Opening a rowset for Sheet1 failed. Check that the object exists in the database"
Note both the files are in xls format
Not sure, and couldn't figure out what's going on
Any suggestions??
-
If the users creates excel file using 2007 and save it as xls format, do we need to specify extended properties as Excel 8.0 or Excel 12.0
-
Excel 12.0 ACE can handle both .xls and .xlsx files
-
Hi Sandy,
Sorry for the late answer, there is a time difference, I was able to duplicate the error message you received
Error: 0xC0202009 at DFT Derived Column, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
Error: 0xC02020E8 at DFT Derived Column, Excel Source [1]: Opening a rowset for "Customer$" failed. Check that the object exists in the database.
Error: 0xC004706B at DFT Derived Column, SSIS.Pipeline: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
I have a test project processing 3 Excel 2003 files, I copy and pasted one of them into a new XL 2010 file, saving it with a new name, and bingo, it returned the error above, it failed to find the worksheet 'Customer$' even when having it. I sorted out this problem by replacing the connection string:
bad connection string
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SSIS_PDS\Temp\Ch05\Customer_June1.xls;Extended Properties="EXCEL 8.0;HDR=YES";Good connection string
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SSIS_PDS\Temp\Ch05\Customer_June1.xls;Extended Properties="EXCEL 8.0;HDR=YES";Once the connection string was replaced it worked fine.
The next test was to rename its worksheet, the package expect the 'Customer' worksheet, if I rename it to something else, I got the same error message.
It seems your problem is:
1. The worksheet name is different, as the file is generated by a different user, it could be possible he or she is using a different name.
2. You are using the old connection string and not the latest one.
We will know more once you check out the two possible reasons, let us know,
Cheers,
Hope this helps,
Rock from VbCity -
Do the ACE drivers automatically install with SSIS or do you have to download them? Last I heard, they still had to be downloaded and installed separately.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I think you are right....I m using bad connection string(as until now we always used to get files in 2003 format)
I will try with ACE 12.0 and will let u know
Thanks for your help
-
Sandy,
Jeff point is important, my workstation has Office 2003 and 2010 installed, so the ACE driver was available, you may need to download the driver from Microsoft if you don't have Office 2007 or 2010 installed on your workstation or the server hosting your package.
Cheers,
Hope this helps,
Rock from VbCity -
Thanks.....that worked.....
-
One more question. I am guessing in the Extended Properties for Provider if i specify "Excel 12.0" both xls and xlsx files will be processed...If i specify "EXCEL 8.0" only xls files will be processed..Is that correct???
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SSIS_PDS\Temp\Ch05\Customer_June1.xls;Extended Properties="EXCEL 8.0;HDR=YES";
-
Yes, that's correct, it could mean a lot of work for you updating any package with Excel data sources based on the old driver (Xl-2003) and people at your site switching to Excel 2007 or 2010; you should update them, as well as any Foreach component searching for *.xls, they will miss the latest Excel files, you should change their *.xls with *.xls*
Hope this helps,
Rock from VbCity
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply