February 27, 2012 at 5:02 am
Hi,
I am using a 64-bit machine . when i got to transfer the data form excel source , I can create a package and run it in 32- bit mode by changing the property in project properties, only at design time if i am correct. What if, my server where i am going to deploy the package is 64-bit , how would i run on my server machine..
Any help would be really appreciated
Thanks and Regards
Chaithanya M
February 27, 2012 at 10:01 am
you can set the property to 32 bit when you deploy it
February 27, 2012 at 9:25 pm
Hi,
I have set the property Run64bitruntime to false..still I do got this error :
[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Breaking my head.Any help would be really appreciated .
thanks and regards
chaithanya M
February 27, 2012 at 11:37 pm
1. Which version of Excel are you using? There are 64-bit providers available for the ACE OLE DB provider.
2. Do you get the error in BIDS or at server? The error indicates that a connection could not be made. What is the connectionstring to the Excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 12:07 am
Hi,
I am using Microsoft Excel 2010. But i have saved my excel file with extension ".xls" . Also, I have changed Run64bitruntime property to false and tried to execute . still it throws the same error. I ran in the debug mode. I still didnt deployed the application , i am working in my local machine only.
I am trying to loop throuhg excel files in a folder and then loop through all excel sheets inside a file. Looks like everything is set well..
Created 4 variables, 1.FileName 2.FolderPath 3.SheetName 4.FilePattern.
Also added expression to the connection managers.dont know where i am doing wrong..
It would be great if you help me out
Regards
Chaithanya M
February 28, 2012 at 12:14 am
It would be great if you posted the connectionstrings and the expressions that build them.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 12:23 am
--Connection string for Ado.net
ConnectionString : "Data Source=G:\EXCEL TO SSMS\wave2_CM.xls;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=EXCEL 8.0;"
Expression :@[User::FileName]
FileName variable is set with default value "G:\EXCEL TO SSMS\wave2_CM.xls" in variables window.
I have three more variables created and there defualt values are:
FolderPath -G:\EXCEL TO SSMS
SheetName-PEC$
FilePattern-*.xls
Regards
Chaithanya M
February 28, 2012 at 12:32 am
Why are you using ADO.NET instead of an Excel source?
Try putting double quotes around your filepath and around the extended properties:
Data Source="G:\EXCEL TO SSMS\wave2_CM.xls";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 12:41 am
Hi,
WOW! that worked. But i see this error : "[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
" again in the Progress tab ..dont know why
Rows got inserted into the desination ..i dont why.it is giving this error still 🙁
Regards
Chaithanya M
February 28, 2012 at 12:46 am
Did you set the DelayValidation property of the dataflow to TRUE?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 2:18 am
Hi Koen,
I really have to thank you for all your replies . I have changed the Delayvalidation Property to True for dataflow task but still, the error persists :
[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
I see these 4 errors after i ran package .
Regards
Chaithanya M
February 28, 2012 at 2:23 am
Are you able to open the Excel Source editor and do a preview of the data?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 2:49 am
I see the Excel sheet is in Compatibility mode, but i can open the excel source editor and view the data.
regards
Chaithu
February 28, 2012 at 2:54 am
OK.
Is it possible to post the exact expression that sets the connectionstring to the Excel file?
Also make sure the Excel file is closed when you run the package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 3:12 am
Excel Connection string :
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\EXCEL TO SSMS\wave2_CM.xls;Extended Properties="Excel 8.0;HDR=YES";
I am just mapping ServerName to --FileName(variable) --> contains the value like "G:\EXCEL TO SSMS\wave2_CM.xls"
and ExcelFilePath ---> SheetName(variable) --Contains the value like "PEC$' --sheetname
I always Close the excel file , when i run the package.
One thing i dont i understand is, how come data getting populated to the tables , though there are some errors : doesn't this stopping the process of loading records into the destination
Regards
Chaithu
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply