+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

RE: Run a ssis package on SQL Server 2005 Error

  1. RE: Run a ssis package on SQL Server 2005 Error

    I have had this problem before and I cannot seem to fix it now. There is a
    post with the same subject line to review.

    I think the solution I used is method 3 from Article ID 918760 but now I am
    not able to edit the Command line in the SQL server Agent Job I think I used
    /DE "password" but I put that line in and say OK but it does not save.

    Any help

    Thank you
    --
    Jerry

  2. RE: Run a ssis package on SQL Server 2005 Error

    Hello Jerry,


    This is supposed to be the normal behavior since it's to protect
    information in the command line. Each time it's edited, it's prompted for
    default command line. However, when it's saved, it's actually saved. Please
    note, you should select the connections used in the packge under "data
    source" tab before you try to change command line.

    If the issue persists, you may try to check if you could run the pcakge
    directly in command prompt by using your password.


    dtexec /SQL "\packagename" /SERVER "." /MAXCONCURRENT " -1 "
    /CHECKPOINTING OFF /REPORTING E /De "Password"


    If you have any update, please feel free to let's know. Thank you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Community Support
    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscripti...ult.aspx#notif
    ications
    .
    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    .
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


  3. RE: Run a ssis package on SQL Server 2005 Error

    Peter,

    Thank you for the reply. I selected the connection under data source and
    then selected the command line and selected edit. Now even without editing
    the command line I get a error :

    The connection string format is not valid.
    It must consist of one or more components of the form X=Y, separated by
    semicolons. This error occurs when a connection string with zero components
    is set on database connection manager.

    The package did not run so I opened the job selected step and when I clicked
    data source I get the error.

    The string in the command line is:

    /SQL "\Triveni cvs" /SERVER xxxxxxxxxx /CONNECTION
    SourceConnectionOLEDB;"\"Data Source=xxxxxxxxx;User ID=xxxxxess;Initial
    Catalog=ContentDepot;Provider=SQLNCLI.1;Persist Security Info=True;Auto
    Translate=false;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

    xxxx inserted by me.

    So even without editing there are errors. I cannot insert the De =
    "password" .
    The connection string is in the command line with quotes and excaped like
    "/" and so on why is the default command line erroring.

    The package runs in SQL server 2005 under the Integration Services when I
    select it and execute the package. So I think the connection string is OK
    there.

    It seems that I still cannot impliment the step of Article ID 918760 Method 3
    by modifying the SQL Server Agent job step command line to include the
    password.

    Jerry


    ""Peter Yang[MSFT]"" wrote:

    > Hello Jerry,
    >
    >
    > This is supposed to be the normal behavior since it's to protect
    > information in the command line. Each time it's edited, it's prompted for
    > default command line. However, when it's saved, it's actually saved. Please
    > note, you should select the connections used in the packge under "data
    > source" tab before you try to change command line.
    >
    > If the issue persists, you may try to check if you could run the pcakge
    > directly in command prompt by using your password.
    >
    >
    > dtexec /SQL "\packagename" /SERVER "." /MAXCONCURRENT " -1 "
    > /CHECKPOINTING OFF /REPORTING E /De "Password"
    >
    >
    > If you have any update, please feel free to let's know. Thank you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Community Support
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/subscripti...ult.aspx#notif
    > ications
    > .
    > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 1 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions or complex
    > project analysis and dump analysis issues. Issues of this nature are best
    > handled working with a dedicated Microsoft Support Engineer by contacting
    > Microsoft Customer Support Services (CSS) at
    > .
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >


  4. RE: Run a ssis package on SQL Server 2005 Error

    Peter,

    Some more information:
    I used the dtexec command to run the command line and the package works
    using this:

    dtexec /SQL "\Triveni cvs" /SERVER xxxxxxxxxx /CONNECTION
    SourceConnectionOLEDB;"\"Data Source=xxxxxxxxx;User ID=xxxxxess;Initial
    Catalog=ContentDepot;Provider=SQLNCLI.1;Persist Security Info=True;Auto
    Translate=false;password=xxxxx\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

    xxxx inserted by me. I used the passoword=xxxx in the command string
    When I used the /De "xxxxxx" it did not work. It seems we are very close to
    fixing this thing if I could just edit the command line.

    Thank you

    --
    Jerry


    ""Peter Yang[MSFT]"" wrote:

    > Hello Jerry,
    >
    >
    > This is supposed to be the normal behavior since it's to protect
    > information in the command line. Each time it's edited, it's prompted for
    > default command line. However, when it's saved, it's actually saved. Please
    > note, you should select the connections used in the packge under "data
    > source" tab before you try to change command line.
    >
    > If the issue persists, you may try to check if you could run the pcakge
    > directly in command prompt by using your password.
    >
    >
    > dtexec /SQL "\packagename" /SERVER "." /MAXCONCURRENT " -1 "
    > /CHECKPOINTING OFF /REPORTING E /De "Password"
    >
    >
    > If you have any update, please feel free to let's know. Thank you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Community Support
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/subscripti...ult.aspx#notif
    > ications
    > .
    > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 1 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions or complex
    > project analysis and dump analysis issues. Issues of this nature are best
    > handled working with a dedicated Microsoft Support Engineer by contacting
    > Microsoft Customer Support Services (CSS) at
    > .
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >


  5. RE: Run a ssis package on SQL Server 2005 Error

    Hello Jerry,

    Thank you for your reply. From the symptoms, it seems that you don't save
    password properly in your package. Did you check "save password" in
    connection manager in VS2005 before you deploy the package to SSIS?

    I used the following command to test and it works fine:


    dtexec /SQL "\dotnetconnection" /SERVER "." /CONNECTION "Flat File
    Connection Manager";"C:\sql\ssistest\dotconnectiontest.txt" /CONNECTION
    "test.mytest";"\"Data Source=sha-petery-01;User ID=sa;Initial
    Catalog=mytest;Pooling=False;Password=password\"" /MAXCONCURRENT " -1 "
    /CHECKPOINTING OFF /REPORTING E /De "password"


    Based on my further test, I found that without connection argument, it also
    works fine both in dtexec and job exectuion.

    dtexec /SQL "\dotnetconnection" /SERVER "." " /MAXCONCURRENT " -1 "
    /CHECKPOINTING OFF /REPORTING E /De "password"

    If the issue still occurs, will you let's know the exact error message you
    see when you run following commands?


    dtexec /SQL "\Triveni cvs" /SERVER xxxxxxxxxx /CONNECTION
    SourceConnectionOLEDB;"\"Data Source=xxxxxxxxx;User ID=xxxxxess;Initial
    Catalog=ContentDepot;Provider=SQLNCLI.1;Persist Security Info=True;Auto
    Translate=false;password=xxxxx\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
    /De "password"


    dtexec /SQL "\Triveni cvs" /SERVER xxxxxxxxxx " /MAXCONCURRENT " -1 "
    /CHECKPOINTING OFF /De "password"


    I look foward to your reply. Thank you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support


    =====================================================

    When responding to posts, please "Reply to Group" via your
    newsreader so that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


  6. RE: Run a ssis package on SQL Server 2005 Error

    Peter,

    Thank you for your reply,

    I can run the package OK from the command line with the commands you posted.

    The problem I am having now is I cannot edit the command line in SQL Server
    Agent. I select the Job and select properties. then Select Setps and Edit
    then select Data sources and select the SourceConnection I need to edit then
    select Command line then select "Edit the command line manually" and then I
    can edit the command line and add the "password=xxxxxx" in the connection
    string or the "De "xxxxx" in the command. Then I press OK There is no save
    button. When I try to run the Job I get the error about a failed login that
    indicates that there is no password. The very one that I just entered. When I
    go back to the command line the password is not in there. It was not saved.
    So there does not seem to be a way to edit the command line even though there
    is a selection that allows editing of the command line.

    At this point the package will run in VS2005, I can run it in ssis, I can
    run it from the command line. But cannot run it form SQL Agent.

    It seems to me there is something very simple that I am missing about
    editing the command line in SQL Agent. There is a edit function but no save
    button or the user I am logged in as does not have permissions or something
    but the Editor is not telling me what is wrong It just does not save the line
    I typed in.

    I have also checked the save password in VS2005 but it also will not save
    the password.If the command string needs a password there should be a way to
    save the password. At this point the only way I can get the password in the
    command line is the use the dtexec command line. Maybe the password is in the
    command line but encripted but there is a way to unencritpt it. I just need
    to know what it is.
    --
    Jerry


    ""Peter Yang[MSFT]"" wrote:

    > Hello Jerry,
    >
    > Thank you for your reply. From the symptoms, it seems that you don't save
    > password properly in your package. Did you check "save password" in
    > connection manager in VS2005 before you deploy the package to SSIS?
    >
    > I used the following command to test and it works fine:
    >
    >
    > dtexec /SQL "\dotnetconnection" /SERVER "." /CONNECTION "Flat File
    > Connection Manager";"C:\sql\ssistest\dotconnectiontest.txt" /CONNECTION
    > "test.mytest";"\"Data Source=sha-petery-01;User ID=sa;Initial
    > Catalog=mytest;Pooling=False;Password=password\"" /MAXCONCURRENT " -1 "
    > /CHECKPOINTING OFF /REPORTING E /De "password"
    >
    >
    > Based on my further test, I found that without connection argument, it also
    > works fine both in dtexec and job exectuion.
    >
    > dtexec /SQL "\dotnetconnection" /SERVER "." " /MAXCONCURRENT " -1 "
    > /CHECKPOINTING OFF /REPORTING E /De "password"
    >
    > If the issue still occurs, will you let's know the exact error message you
    > see when you run following commands?
    >
    >
    > dtexec /SQL "\Triveni cvs" /SERVER xxxxxxxxxx /CONNECTION
    > SourceConnectionOLEDB;"\"Data Source=xxxxxxxxx;User ID=xxxxxess;Initial
    > Catalog=ContentDepot;Provider=SQLNCLI.1;Persist Security Info=True;Auto
    > Translate=false;password=xxxxx\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
    > /De "password"
    >
    >
    > dtexec /SQL "\Triveni cvs" /SERVER xxxxxxxxxx " /MAXCONCURRENT " -1 "
    > /CHECKPOINTING OFF /De "password"
    >
    >
    > I look foward to your reply. Thank you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Partner Support
    >
    >
    > =====================================================
    >
    > When responding to posts, please "Reply to Group" via your
    > newsreader so that others may learn and benefit from this issue.
    > ======================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >


  7. RE: Run a ssis package on SQL Server 2005 Error

    Hello Jerry,

    As I mentioned, it's normal that the command line shown after you save is
    not the same as you have saved. Also, when you check "save password" in VS
    2005, you will still be prompted to input password again when you edit the
    connection. However, the password is actually stored in the package.

    I'd like to confirm that the both commands work properly for your server


    dtexec /SQL "\Triveni cvs" /SERVER "xxxxxxxxxx" /CONNECTION
    SourceConnectionOLEDB;"\"Data Source=xxxxxxxxx;User ID=xxxxxess;Initial
    Catalog=ContentDepot;Provider=SQLNCLI.1;Persist Security Info=True;Auto
    Translate=false;password=xxxxx\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
    /De "password"


    dtexec /SQL "\Triveni cvs" /SERVER "xxxxxxxxxx" /CHECKPOINTING OFF /De
    "password"

    I still suspect that the command line you use in command line window is not
    exact same as that works in command prompt.


    If so, please just copy the commands line without "dtexec" when you edit
    command line to replace the existing command. After that, click OK to save,
    and run the job at this time to test. You may try both commands one by one.

    Actually, you could work around the issue by running command directly in
    job step by choosing operation system(cmdexec) type, and then use the
    command directly:


    dtexec /SQL "\dotnetconnection" /SERVER "xxxxxxxxxx" /CHECKPOINTING OFF
    /REPORTING E /De "Password01!"

    If you have any update, please feel free to let's know. Thank you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support


    =====================================================

    When responding to posts, please "Reply to Group" via your
    newsreader so that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


  8. RE: Run a ssis package on SQL Server 2005 Error

    Hello Jerry,

    I'm still interested in this issue. If you have any comments or questions,
    please feel free to let's know. We look forward to hearing from you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support


    =====================================================

    When responding to posts, please "Reply to Group" via your
    newsreader so that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


  9. RE: Run a ssis package on SQL Server 2005 Error

    Peter,

    Thank you for the reply.

    The point I missed was I needed to put the entry password=xxxxx in the
    connection string part of the command line and the /De "xxxxx" also. I then
    hit OK and the package runs when I select run job.

    There is still issues If I go back into the Job and try to edit the step
    command line again. There is the error I mentioned about
    The connection string format is not valid.

    This error shows when I try to select the Data Sources tab. After that the
    package must be deleted and reinstalled. If I close the editor and restart it
    the connection is missing and the package does not run.

    I Think I have the package running now. I still have to see if it will run
    when scheduled but it will run if I select it and do a run from step. This
    ssis stuff is to buggy to use I think if I have to do this again I will write
    a exe and schedule it from the operating system.
    --
    Jerry


    ""Peter Yang[MSFT]"" wrote:

    > Hello Jerry,
    >
    > I'm still interested in this issue. If you have any comments or questions,
    > please feel free to let's know. We look forward to hearing from you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Partner Support
    >
    >
    > =====================================================
    >
    > When responding to posts, please "Reply to Group" via your
    > newsreader so that others may learn and benefit from this issue.
    > ======================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >


  10. RE: Run a ssis package on SQL Server 2005 Error

    Hello Jerry,

    Thanks for your update and nice to see the job works. As I mentioned, the
    error regarding data source should be related to the name of connection
    manager, and you may want to rename the connection manager to a simple name
    such as test etc to see if the issue persists.

    Also, based on further my test, it's not necessary to include /CONNECTION
    switch or check connections in "data source" tab when configure command
    line. You may test following command directly:


    dtexec /SQL "\Triveni cvs" /SERVER "xxxxxxxxxx" /CHECKPOINTING OFF /De
    "password"


    If it works, you could use following in comand line:

    /SQL "\Triveni cvs" /SERVER "xxxxxxxxxx" /CHECKPOINTING OFF /De "password"

    If anything is unclear, please feel free to let me know. Thank you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support


    =====================================================

    When responding to posts, please "Reply to Group" via your
    newsreader so that others may learn and benefit from this issue.
    ======================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


+ Reply to Thread
Page 1 of 2 1 2 LastLast