March 8, 2011 at 10:44 pm
Hi,
I was trying to loop through a folder for reading excel files into SQL Server database. I have configured everything and it is working well till I made the variables configurable (SQL server configuration). Basically my package will loop through the excel files specified in a folder and dump the data into SQL server and archives the file in a different location. For this I had a foreach loop container in which I had a Data flow task and File system task which does the job.
After completing the configuration of the variables, the package started failing with the below error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "PMTFiles_Conn" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I had the Excel File path set to point to a correct variable and the folder path from which the files have to be read is also fetched correctly from the database. Could not understand where I'm going wrong. If I disable the DFT and execute the foreach loop container, it works fine with the configured values in the database.
Have checked blogs and followed the same process defined there without any success...
Please help me to solve this problem. Thanks in advance.
Regards,
Krishna Chaitanya
March 9, 2011 at 12:04 am
Set the DelayValidation property of the dataflow to TRUE.
See if that helps...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 9, 2011 at 2:23 am
Do you also get this error?
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
If yes, you may need to run your package in 32 bit environment?
Please let me know more details on configuration an version.
March 9, 2011 at 5:17 pm
Hi,
I have set the delay validation property to true on the package as well as the connection and tested. It fails there as well. I have another similar packages which have to load data from CSV files and XML files into SQL server which are working absolutely fine. Only problem is with the Excel files and now I have no clue whats happening.....
Regards,
Krishna Chaitanya
March 9, 2011 at 11:49 pm
Allright, first of all, as Preethi S Raj mentioned, are you developing on a 32-bit machine or a 64-bit machine?
Are there any other errors?
Is it possible to set a breakpoint at the beginning of the dataflow and check the values of the variables?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 6:18 pm
Hi Koen, Preethi,
Thanks for your help. The problem is with the 64 bit environment only as mentioned. I have Set the Run64BitRuntime property to false and it started working as expected... 🙂
Regards,
Krishna Chaitanya.
March 16, 2011 at 10:14 pm
Hi,
Now I'm having problem while scheduling the package to run through the SQL job. The package is running successfully through the execute package utility but it is always failing through the SQL server job. I'm running the job in 32 bit environment only but still fails. The error message that it is giving is as below:
Executed as user: ******. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Option "8.0;HDR=NO;IMEX=1;;" is not valid. The command line parameters are invalid. The step failed.
The command line created in the SQL job is:
/SQL "\PMT_Import" /SERVER ****** /CONNECTION "Config_Conn";"\"Data Source=******;Initial Catalog=*****;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;\"" /CONNECTION "Test_Conn";"\"Data Source=*****;Initial Catalog=*****;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package1-{93CFDE27-19E5-4FBF-AB4C-FE3231560B20}******;Auto Translate=False;\"" /CONNECTION "PMTFiles_Conn";"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Test1.xls;Extended Properties=""EXCEL 8.0;HDR=NO;IMEX=1;"";\"" /X86 /CHECKPOINTING OFF /REPORTING E
I'm unable to understand why the job is failing when the same package is working as expected when running through the execute package utility. Kindly suggest...
Regards,
Krishna Chaitanya.
March 17, 2011 at 12:37 am
How exactly are you scheduling the package in the SQL Agent job?
Are you using a Integration Services jobstep?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 17, 2011 at 4:27 pm
Yes. I'm using Integration services Job step in calling the package.
March 18, 2011 at 12:48 am
How did you configure this jobstep?
Did you use a configuration file? Is it possible to post some screenshots? (you can add attachments to your post to upload them to this site)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2011 at 10:42 pm
Hi Koen,
I have attached the screenshots of the job step configuration.
I also have tried executing the package through CMDExec which also failed.
Please let me know where I'm going wrong.
Regards,
Krishna Chaitanya.
March 23, 2011 at 7:28 am
Allright, I've noticed two things:
1. I don't check my connections in the SSIS jobstep data sources tab. That is if you want to override the configured connectionstrings from within the job. If this isn't necessary, you don't need to check them.
2. In the Excel connectionstring, there is a semicolon too many in the option piece:
Extended Properties=""EXCEL 8.0;HDR=NO;IMEX=1;"";
Remove that semicolon and see if it works.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 6:20 am
Hi, I'm facing exactly same problem, how did you manage to fix it??
Package runs fine when I run thru DTS exec utility, but fails if I run the package inside a SQL Job
Option "8.0;HDR=NO;" is not valid.
June 14, 2012 at 6:22 am
sorted, A bug with SQL Server, inserts funny connection string, used a script to create the job, all works fine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply