June 28, 2010 at 6:57 am
I am getting the following error when connecting to an excel file
Option "Source=D:\Upload_Dump\XXXXXX.xls;Extended" is not valid. The command line parameters are invalid.
the file is present and the package runs perfectly well in design mode. Running the package through the SQL Agent givesthis error, so it could be security, but I can't see why as I have other packages which import succesfully (also excel files) from the same directory.
Any ideas?
Thx
October 4, 2010 at 4:47 am
Anyone have any ideas on this?
October 4, 2010 at 5:53 am
Are you running the SSIS package in 32 or in 64-bit mode?
It also seems that your connection string is incomplete (or you copy pasted just a small part of it). In my packages, a connection string to an excel file looks like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\MyServer\MyFile.xls";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
October 4, 2010 at 7:29 am
The package is running fine in Visual Studio (and also if I run the *.dtsx file outside of VS 2005), and although I didn't post my whole connection string, I am sure that this is not the culprit.
The package fails when it is scheduled in the SQL Agent. The package uses a foreach loop container to run through and search for excel files, and pick up any that match the filename rules.
a using a 32 bit system - I have this sort of process running for csv files and it works perfectly. It seems that the excel is a little more tricky!
:crazy:
October 4, 2010 at 8:15 am
And what about configurations? Do you use config files/tables?
There must be some reason why the set-up is different in SQL Server Agent.
Are all the permissions set-up correctly?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 4, 2010 at 2:44 pm
And what about configurations? Do you use config files/tables?
Where would these be used? I haven't used anything like that at all...
October 5, 2010 at 1:10 am
OK, so everything is hard coded into the package?
No use of variables somewhere that can change during runtime?
Does the SQL Server Agent account has the necessary privileges to access the Excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 5, 2010 at 4:00 am
Does the SQL Server Agent account has the necessary privileges to access the Excel file?
Sounds like this might be the cause....how can I check this though?
Thx
October 5, 2010 at 4:16 am
Jason Coleman (10/5/2010)
Does the SQL Server Agent account has the necessary privileges to access the Excel file?
Sounds like this might be the cause....how can I check this though?
Thx
Check the following URL's:
http://msdn.microsoft.com/en-us/library/ms186264(v=SQL.90).aspx
and
http://msdn.microsoft.com/en-us/library/ms191543(v=SQL.90).aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 5, 2010 at 7:15 am
I've actually just found a solution to the problem.....
INstead of scheduling the job normally in SQL Agent, and checking all the boxes for the connections etc, I checked the radio button "Edit the command line manually"on the Command Line tab.
The job now runs without any problems at all!:w00t::w00t:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply