ConnectionString:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=" + @.[User::ExtProperties]
(the ExtProperties was necessary to get the double quotes in. )
ServerName:
@.[User::FileName]
It cannot connect.
I used a similar process to loop thru Access .mdb files, but did not set the ServerName and did not need the ExtProperites because the Access connection did not need them, and I got that to work.
Any help would be very much appreciated.Did you get an error message with any useful information?
You should not need to specify "Server." And I'm not clear on the need to use a variables for Extended Properties, since they're probably not changing from one Excel file to another, and no extra quotes are necessary if your string is simply "Extended Properties=Excel 8.0," assuming the default value for HDR and not using IMEX.
You could use a short Script task within your loop to view the connection string resulting from the expression on each iteration.
-Doug
|||I used the following expression for Connection String, and was able to successfully loop through all excel files. As Douglas correctly pointed out, you don't need ServerName. Use \ to get your quotes in, if you have to.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::Variable] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"
thanks
Ranjeeta|||
I removed the ServerName expression and I changed my connectstring code to be like Ranjeeta's...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
It evaluates correctly, but When I run, I still get the same errors:
Error at LoadStagingTables [Liabilities[1641]]: The AcquireConnection method call to the connection manager "PlanFinancialsInput" failed with error code 0xC0202009.
Error at LoadStagingTables [DTS.Pipeline]: component "Liabilities" (1641) failed validation and returned error code 0xC020801C.
Error at LoadStagingTables [DTS.Pipeline]: One or more component failed validation.
Error at LoadStagingTables: There were errors during task validation.
Error at ProcessFinancials [Connection manager "PlanFinancialsInput"]: An OLE DB error has occurred. Error code 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".
(Microsoft.DataTransformationServices.VsIntegration)
If I change the connection back to just the first xls file, it works fine, but when I change back to using the connectstring, I get these errors.
Thanks very much for your help.
|||Success!!!!I got it to work. I created a new package and tested a small .xls file just to see if I could get one to work and it worked. I didn't do anything different. But I still couldn't get my original package to work. So I compared properties at various levels and I found one different. I don't know how it got changed, but it was different. The DelayValidation on my original Data Flow task was set to False, but it was True for my test task. I changed it to True, then deleted my connectstring property and change the source to the excel file, then added the connectstring back again, and voila! it works!
|||Were you using the September CTP? I'm running into the same issue and can't seem to get around it as you have.|||
No, the June CTP.
Version 9.00.1187.00
Make sure you have the delay validation property set to true on every data flow task or any task that would use your connection.
|||That did it! We had it set in each task in the data flow, but not the data flow itself. Thanks!|||just looked at the forum.. you could try the following:
1. Foreach loop to process .xls files in a dir - set variable xlsDoc to filename
2. script to change an Excel Connection Manager to the loaded filename
Try
Dim xlConn As ConnectionManager = Dts.Connections("Excel Connection Manager")
xlConn.Properties("ExcelFilePath").SetValue(xlConn, Dts.Variables("xlsDoc").Value.ToString)
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
Exit Sub
End Try
3. Foreach loop to process worksheets (poss load from a list which i'm doing - if you do, then trim the worksheet name from the ADO variable, to remove string padding, or it won't load properly)
4. Call your dataflow which uses the single 'Excel Connection Manager'...
The problem i'm getting now is when a worksheet doesn't exist, trying to trap/cancel the error, i've added handlers to the dataflow task, to the excel connection inside it etc, no joy...
|||Hi
I am using SqlServer 2005 September CTP version.
I have created a package which uses 'Data Flow task' to read a Excel file,
a 'Script Component' which does some lookups and 'Sql Server Destination' to insert data into Sql server 2005 database.
The package runs sucessfully.Then I deployed the package using dtsinstall.exe,
it validate & deployed successfully,but when I tried to run the
package it gives me following error :
"Error : The AcquireConnection method call to
hte connection manager "Excel Connection
Manager" failed with error code 0xC0202009"
"Error : component "Excel Source" (133) failed
validation and return error code 0xC020801C.
I also tried changing DelayValidation to true
but it dosent work after deployment.
please suggest.
Thanks
Abhishek
Hi,
I trying the same process as you have told but not succesfull.
i am getting the error "Excel Connection Manager Failed".
plz suggest me how to remove it.
thanks
|||AkChauhan wrote:
Hi,
I trying the same process as you have told but not succesfull.
i am getting the error "Excel Connection Manager Failed".
plz suggest me how to remove it.
thanks
Check this out; I think it has what you need. Otherwise, open a new thread and provide the specific errors you are getting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278469&SiteID=1
|||hi Ranjeeta,
i am having the same issues for looping the excell files. i has used all the settings for the same which you and all others has said but not successful. i am getting some connection manager failed and invalid argument error. Now i dont know what to do for it. plz help.
thanks....
No comments:
Post a Comment