none
SQL agent parameter passing

    Question

  • Hi,

    I need to pass a parameter in SQL agent job. SQL agent job is not accepting it, and throwing an error like,

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Below is the value

    Provider=OraOLEDB.Oracle.1;Data Source=XYZ;Persist Security Info=True;User ID=User1;Extended Properties="FetchSize=2000;ChunkSize=2000;"

    If I remove the "" it is working but it is not the correct value to send to the SSIS package which I'm running via the SQL agent job.

    Please help!!

    Thursday, May 10, 2018 11:55 AM

Answers

  • You should not need to do this from the command line.  If you use the SSIS type,  you can change these things much easier in the SSIS properties.

    However, if you must, you need to use 2 double quotes:

    Provider=OraOLEDB.Oracle.1;Data Source=XYZ;Persist Security Info=True;User ID=User1;Extended Properties=""FetchSize=2000;ChunkSize=2000;""

    Thursday, May 10, 2018 12:18 PM
    Moderator

All replies

  • You dont need to include "

    you can just specify the connectionstring value alone in the Set values tab

    just specify property path as below

    \Package.Connections[connection manager name].ConnectionString

    not sure whether you're trying to pass it through Set values tab or directly specify in the configuration options

    May be you can elaborate with screenshots


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, May 10, 2018 12:18 PM
    Thursday, May 10, 2018 12:10 PM
  • You should not need to do this from the command line.  If you use the SSIS type,  you can change these things much easier in the SSIS properties.

    However, if you must, you need to use 2 double quotes:

    Provider=OraOLEDB.Oracle.1;Data Source=XYZ;Persist Security Info=True;User ID=User1;Extended Properties=""FetchSize=2000;ChunkSize=2000;""

    Thursday, May 10, 2018 12:18 PM
    Moderator
  • Hi,

    I'm trying to compare the connection string in the cube with the connection string passed from the SQL agent job.

    Reason for doing it, I will pass the connection string from the sql agent job which is expected in the cube. If cube is having the same connection string it is fine. If not, the job will fail and trigger a mail.

    Why we are doing this - Some times it is observed that after deployment, team forget to re-point the connection to point to right server. And the partitions are processed with data from wrong server.

    To avoid this situations, checking the cube connection using AMO and value passed from sql agent job and comparing both in C# in a script task.

    str_connectionstring=db.datasources.getbyname(SSASConnectionstringname).ConnectionString.ToString();The

    The above value matched with the connection value from the sql agent job.

    Using double quotes, SLQ agent job is able to take the value but in the C# code comparison, it is failing with the value coming from the Cube.

    Friday, May 18, 2018 10:24 AM