September 26, 2008 at 11:35 am
Hi, We have ten excel files in a folder which we want to load in database. We are trying to create a package to read all the files one by one. For some reason it always reads the first file. The for loop is working fine incase of text files.
September 26, 2008 at 11:44 am
Is the structure of all the Excel file same - Are they going to insert in one single table?
Also are you building the connection dynamically using the loop?
September 26, 2008 at 12:43 pm
Hi Mukti, Thanks a lot for your help. this is my detailed issue.
The need is the upload 1 row of multiple xls files each to the database from a folder. Previously the issue that we were facing was that the excel file manager was accepting only 1 excel file, and using the foreachloop container, it was uploading the same file's data as many number of times as there were files in that folder. On top of it, the SSIS excel manager supposedly expects the file configured as a sample, always to be existing, unlike DTS, where-in the file was needed just during mapping the columns to the databe, and after which, whether or not the sample file existed in the folder, DTS never cared.
But SSIS seemingly, more than cares. It bombs.
So not to wastes anymore time investigating plausible solutions, (ok let me tell you that we use these dts packages in callign from our batch windows files), I wrote a for loop in the batch file itself and then before calling the SSIS package, rename every file in that directory to the name which SSIS has as its sample file, and then after the package execution gets completed, delete or move the file, so that the next time it tries to rename the next file to the same name, it does not fail.
This seemed to work like a charm.
Now SSIS somehow got the hint that we were able to get through its impenetrable shroud of useless complications, and decided to play with us more. Now the scene is such that it is supposed to be data dependant and densely excel formatting dependant as well. So I had one xls file which got uploaded fine and the next one did not. So we copied the cells from the working xls to the not working one and it worked. Now with the same formatting, if we have a NULL value in any of the cells, then it fails again.
So null is different, no matter whatever format the cell is.
The problem is, we have 150 columns in our table, and looking into 150 fields and finding out which format works, did not sound that provocative. But, what we are trying to do now is fill in data in the working xls file and make sure there are no nulls and then make it to work and then make it a mandate that all the excels be in the same format.
Just wondering if there' s any other way around ?
September 26, 2008 at 1:49 pm
I guess your foreach loop in ssis didn't work becoz you were not dynamically changing your excel connection - so even though you were picking excel files in the for loop.
1. Create Excel connection
2. create variable vExcelfileName
3. Use vExcelfileName is varibale mappings of foreach loop task.
4. In Excel file connection, go to expressions - > Select ExcelFilePath and put vExcelFileName in the expression box. this will make the conneciton dynamic.
The above will work if all the excel file have same structure.
Regarding null values - Please send the exact error you are getting.
September 26, 2008 at 2:13 pm
Thanks a lot. I have already used a global variable:- gv_FileName, it appears in the foreachloop variable mappings as User::gv_FileName.
Now in the expressions of the excel connection manager, I have the connection string has the value:-
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::gv_ExcelFile] + ";Extended Properties=\"EXCEL 8.0;HDR=YES;IMEX=1\";"
And the ExcelFilePath property has the value:-
@[User::gv_FileFullName]
For the null values, I cannot replicate it right now, as I have changed my package a lot, but it was giving something like:-
The external metadata column collection is out of synchronization with the data source columns. The column "CMNTS_ON_FIN" needs to be updated in the external metadata column collection.
And it automatically asked me whether to correct this metadata difference. So essentially, I was clicking on Yes, for every different file I opened in the excel connection manager, as some fields were always null, and somewhat different in cell-formatting.
September 26, 2008 at 2:25 pm
If you are using fully qualified name in Foreach loop then you don't have to do anything in the connection string. Just put the variable that you used in the Foreach loop mapping in the expression ExcelFilePath. It will build the connection string automatically for you and you don't have to worry about building it yourself
So in short you just need one variable and this will keep the package simple and more managebale.
September 26, 2008 at 2:33 pm
I am sorry I did not understand..
What exactly to put in the connection string and the ExcelFilePath properties ?
Given my variables in the above reply, can you please let me know the exact value?
September 26, 2008 at 2:36 pm
In your excel file connection - > Expressions - >ExcelFilePath->@[User::gv_FileName]
September 26, 2008 at 2:41 pm
Hey thanks a ton!
Can you please give some pointers on the null value problem? Also we have few more issues:
1. How to initialize global variables in SSIS with the values in an ini file?
September 26, 2008 at 2:55 pm
What package properties are you configuring using ini file? I have never used ini files for package configs. SSIS provides config using Environment variable, sql server table, xml.
I would suggest using one of these for package configurations.
Regarding null issue - could you send the exact error you are getting?
September 26, 2008 at 3:01 pm
For the null values, I cannot replicate it right now, as I have changed my package a lot, but it was giving something like:-
The external metadata column collection is out of synchronization with the data source columns. The column "CMNTS_ON_FIN" needs to be updated in the external metadata column collection.
And it automatically asked me whether to correct this metadata difference. So essentially, I was clicking on Yes, for every different file I opened in the excel connection manager, as some fields were always null, and somewhat different in cell-formatting.
will this suffice?
September 26, 2008 at 3:02 pm
Also when I tried the option which you had suggested regarding the for loop, it gave me the following error.
Error: 2008-09-26 16:53:51.32
Code: 0xC0202009
Source: d_ccrs_obligor_stg_ld Connection manager "Excel Connection Manager 2"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine could not find the object 'D:\DTSPackages\CCRS\LOADREADY\BLESSEY ENTERPRISES INC_0805238_9002_09_08.xls'. Make sure the object exists and that you spell its name and the path name correctly.".
End Error
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply