Post reply

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed.

  • January 3, 2012 at 11:54 am

    #252811

    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

  • January 3, 2012 at 11:57 am

    #1427926

    in Set Values tab,I set

    I set Properties Path to:

    \Package001.Variables[User::ConnectionString001].Properties[Value]

    and Vaule is the actual connection string.

    but still got the same error.

  • August 20, 2012 at 5:40 pm

    #1527894

    http://www.sqlserverbox.com/ssis-package-in-sql-server-agent-job-fails.html

    http://sqlism.blogspot.com/2012/08/ssis-package-in-sql-server-agent-job.html

    SSIS Package in SQL Server Agent Job Fails

    Error:

    Argument "xyz" for option "connection" is not valid. The command line parameters are invalid. The step failed.

    Solution:

    If your SQL Server is 64 bit

    In SQL Server JOB-->Properties-->Steps--->

    In the Execution Option Tab -- Check "Use 32 bit runtime"

    In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.

    Schedule the Job and run it.

  • December 20, 2012 at 4:50 pm

    #1570677

    This did the trick! Thank you so much for posting this solution!

    Unfortunately I wasted a whole day playing with different settings to try to resolve this... wish I had googled this error message first 🙁

    I would have never thought of this in a million years.

  • May 30, 2013 at 10:11 am

    #1619780

    Thank you so much. I have been loosing my hair since yesterday trying to find a solution and finally your solution just worked like magic!!!

  • July 15, 2013 at 9:53 am

    #1632543

    +1 this did the trick for me too!


    Everything is awesome!

  • September 6, 2013 at 8:32 am

    #1648116

    hi, the solution didn't work for me though I had the same error. What I found was that the SQL Connections (in BIDS) can not contain spaces when processed via the SQL Agent.

    - Leon

  • October 3, 2013 at 4:09 am

    #1655485

    Lightly resurrecting this just to note that I had the same problem.

    Forcing it to use the 32-bit executable didn't work, but I did find out that the job had been altered in SSMS 2008 when it's a 2005 database.

    Recreating the steps through SSMS 2005 fixed the issue for me. Thanks for the pointers though! 🙂

  • May 20, 2014 at 2:23 am

    #1714821

    Actually the problem was that in my SSIS package I had the connection names as "server.domain.com". If the connection name contains dots and dashes, then the SQL Agent will fail to execute the package, since these connection names end up in the command line of the dtexec executable.

    Make everything as simple as possible, but not simpler.
    Albert Einstein

  • July 22, 2015 at 3:47 am

    #1814039

    Thanks for your answer 🙂 I had the same problem and I changed the connection names in both Source and Destination and it's fixed 🙂 lot of thanks sibir1us

  • October 20, 2015 at 5:53 pm

    #1834519

    I had a similar issue, the workaround I found was removing all spaces , hopefully may be helpful (may work for some)

    I changed my connection string

    Data Source=ABCD;User ID=USERNAME;Password=PASSWIORDHERE;Initial Catalog=DBNAMEHERE;Provider=SQLNCLI11;

    SERVER=ABCD;UID=USERNAME;Password=PASSWIORDHERE;DATABASE=DBNAMEHERE;Provider=SQLNCLI11;

    Worked like a charm for me after that.

    Unfortunately , command lines utilities(DTEXEC here thru SQLAgent) are never good in handling any spaces :-(, a chronic issue

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply