DTEXEC thorws error saying Argument "Excel" for option "connection" is not valid.
-
I have developed a SSIS package this runs fine in BIDS and even when i run through DTEXEC UI but when i copy the script generated by the DTEXEC UI and run manually through command promt ..... it throws me the below error. I am just copying the syntax generated by the DTEXEC user interface!!! ....any help would be appreciated
Connection names in the package:
(1)Excel-Header-AsRecord
(2)Excel-Header-NotAsRecord
(3)Flat File - 5 columns with header
(4)Flat File - 6 columns with header
(5)Flat File - 8 columns with header
(6)SampleDB --> server is "sql2005"
Script:
dtexec
/FILE "C:\Import\pkg_Load_ExcelTables.dtsx" /CONNECTION "Excel-Header-AsRecord";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CodeExcelFiles_TempTextFiles\CodeTableValueExtract.xls;Extended Properties=""EXCEL 8.0;HDR=NO"";" /CONNECTION "Excel-Header-NotAsRecord";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CodeExcelFiles_TempTextFiles\CodeTableValueExtract.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";" /CONNECTION "Flat File - 5 columns with header";"C:\CodeExcelFiles_TempTextFiles\5_6 Columns header.txt" /CONNECTION "Flat File - 6 columns with header";"C:\CodeExcelFiles_TempTextFiles\5_6 Columns header.txt" /CONNECTION "Flat File - 8 columns with header";"C:\CodeExcelFiles_TempTextFiles\5_6 Columns header.txt" /CONNECTION PSII;"Data Source=SQL2005;Initial Catalog=sampleDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V >> C:\error_log.log /CONSOLELOG T
Error:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Argument "Excel" for option "connection" is not valid.
Thanks in Advance!!!!!
-
A very common issue and a painfuly boring one. It's to do with quotes in your paramters being pasted.
I would suggest, rather than passing them in, rather use a config file (SSIS Configuration files) to set the values. It's a tad neater.
Obviously, you may have to do it this way.
As for the problem, you easiest way is to pass no parameters, then one, then two etc and wait for it to bail.
There a number of params there. Makes me dizzy trying to read them looking for quotes
Update::
Removing the - from the connection manager seems to do the trick.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
hi,
this is my command line:
/DTS "\MSDB\LandRoverDailyPartPricesAudit" /SERVER server name /CONNECTION "server IP.DB Name";"\"Data Source=Server IP;Initial Catalog=DB Name;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /CONNECTION "Excel Connection Manager";"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\LRPartPricesAudit.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";\"" /CONNECTION "LRPartPricesAudit.xls";"R:\LRDocs\LRPartPricesAudit.xls" /CONNECTION "LRPartPricesAudit.xls 1";"R:\LRPartPricesAudit.xls" /CONNECTION "SMTP Connection Manager";"\"SmtpServer=SMTP Server IP;UseWindowsAuthentication=False;EnableSsl=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and this is the error i get when execute the job:
Message
Executed as user: User Name. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Argument "Excel" for option "connection" is not valid. The command line parameters are invalid. The step failed.
please help...
Thanks in Advance!!!!
-
I had same problem, but I got it working by adding two \" inside the quotes of "Excel Connection Manager", that it works. So it looks like this: "\"Excel Connection Manager \"" . Also I did not have two double-quotes around EXCEL 8.0;HDR=YES, just one double quote: Extended Properties="EXCEL 8.0;HDR=YES";
Cheers... Steve
-
We were unable to get any of these remedies to work. What we ended up doing (on a 64-bit Server - may have been the Jet Driver problem to begin with) was saving the excel file as a csv and reading it as a flat-file source - worked first try...
-
sneugebauer (11/19/2008)
I had same problem, but I got it working by adding two \" inside the quotes of "Excel Connection Manager", that it works. So it looks like this: "\"Excel Connection Manager \"" . Also I did not have two double-quotes around EXCEL 8.0;HDR=YES, just one double quote: Extended Properties="EXCEL 8.0;HDR=YES";Cheers... Steve
Thank you, removing one set of quotes around EXCEL 8.0;HDR=YES helped me fix this issue.
I hope MS is working on fixing these bugs, I'm on SQL 2008 SP1...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url] -
The only fix that worked for me on my end was changing the Excel output to a Flat File CSV. Once I had done that, the package ran fine.
-
This worked for me. I was getting this error when i ran the package from a job step. After removing one set of the quotes and replacing as "EXCEL 8.0;HDR=YES", the step went in smooth.
Thanks for the tip.
-
John
Solutions Architect
-
I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at the error:
Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed.
In Job Step Properties, Configuration, Command files, Execution options, Logging, Set Values, Verification tabs do not have anything set. The only change made is in Data Source tab, the server name is changed to server2 ,so is in Command Line tab changed accordingly. But, somehow, it still points to the connection inside of the package. If I change the connection in the package, then create a new job point to the changed package, then it runs fine. Why changing DataSouce in the job properties will not take effect?
Here is what is Command Line tab:
/FILE "C:\ Conv_01.dtsx" /CONNECTION "Connection001";"\"Data Source=server2\SQL2008;Initial Catalog=FO;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{849C75AD-4693-4DF2-9EC7-73C347FEE10C}HEC_1.US-Iv2.5;\"" /CONNECTION " Connection002";"\"Data Source=server2\SQL2008;Initial Catalog=ULL;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{D399B84F-1467-41C3-A292-5B27789B66A3}HEC_EL1.ULL;\"" /CHECKPOINTING OFF /REPORTING E
-
What i understand from your description and If i am correct, you are trying to attempt for a dynamic data source connection.
One thing that you really need to understand that this is not something you can control or modify from a agent job step.
What you need to use to achieve this is "Expressions".
of course, If you change a data source name in the data source tab of the job step, it would reflect in the command line tab but it does not mean that it will be effective when it comes to running the package. You need to make the package understand or so to say configure the package to be able to take the input from the job step and connect to the dynamic data source depending on the value provided from the job step.
So, you need to use expressions. Click on the data source that you expect to be dynamic. Open up the properties tab. Make sure the intended connection is highlighted so as to indicate the correct properties in the tab. expand expressions and click on "...". before you do this, you need to create package variables that would hold the input values from the job step. Assign the appropriate expression to the specific variable that it would depend on. In this case you would need to use the expressions "initial catalog" and "servername". Just a tip, it would be better if you did create a solid connection first and then make it dynamic which would reduce the need of adding the info for connection protocols.
And that's it, use the variables from the job steps to pass in the specific input values and that's it you're good to go.
I can surely elaborate but please let me know if you have specific questions so that i can suggest you exactly what you need to do rather explain the whole thing.
Sound good?
Thanks.
-
John
-
I highlight the connection in Data source tab, but don't see the expression and … that I can click to
You mean in Set Values tab?
I set
Property Path same as the variable name in the package, and Value is the actual connection string. but got the same error.
Thank you very much for your help.
-
I changed Properties Path to:
\Package001.Variables[User::ConnectionString001].Properties[Value]
and Vaule is the actual connection string.
but still got the same error.
-
while executing a SSIS package from schedule JOB in SqlServer I got the below same error ,
*Argument "Excel" for option "connection" is not valid. The command line parameters are invalid.*
I tried all the options by modifying the Command line code from the "Command Line" Tab but nothing works for me.
As my server is running in 64bit and currently SSIS only support 32bit runtime for Excel connection, I had followed the below steps and my SSIS package executed successfully.
1. In the "Execution Option" Tab, select the checkbox "Use 32 bit runtime"
2. Then goto "Data Sources" Tab and uncheck the checkboxs if already checked.this will remove the connection related options from the command line tab which is not required.
3. Schedule the Job & its executed fine.
Hope this help 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply