locked
Running .bat file in ssis RRS feed

  • Question

  • Hi friends,

    I am in a problem and working on it from last two days. I was given a task of extracting some information from a table into a flat file and copying this file to a remote linux box.  So I am using pscp to do the copying. To do the copying I am created a .bat file. And from SSIS script task using C# code I am executing the .bat file. When I run the package from BIDS it run successfully and copies the file to the remote Box. But when I run the same package using a SQL Server agent it runs successfully but do not copy the file. Where am I going wrong? When I run the package from BIDS i see the black CMD window coming up and closing automatically. So i used process.windowstyle.hidden property but still it shows up for some reason.

    I need of urgent help.. Any help and Suggestion is greatly appreciated!!

    Thanks in advance

    Gopu


    Gopal
    Friday, January 13, 2012 7:34 PM

Answers

  • You need to set a proxy which is a domain account based one to execute the package step in the Agent as described here:

    http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx


    Arthur My Blog
    • Marked as answer by Gopal425 Monday, January 16, 2012 5:26 PM
    • Unmarked as answer by Gopal425 Monday, January 16, 2012 5:33 PM
    • Marked as answer by Gopal425 Tuesday, January 17, 2012 10:47 PM
    Monday, January 16, 2012 4:20 PM

All replies

  • For starters, can you include the "pause" command in your .BAT file and execute the SQL job manually and see if the command window opens up?
    Jagannathan Santhanam
    Friday, January 13, 2012 8:36 PM
  • First of all Thank you for ur reply.  As you said I have included pause in the bat file and re ran the script task. I could see black window popup and closing again by itself. it didnt ask me to enter any key to continue. :(
    Gopal
    Friday, January 13, 2012 8:40 PM
  • If I remember correct, when you create a SQL Job step which uses "Operating system (CmdExec)" as the command type, it embeds the steps from the .BAT file you pick. Can you ensure that your job step contains the "pause" command? If not, include it and rerun the job again. I am thinking this may be a SQL Agent permission issue.

     

    Also, try this from SQL Management Studio Query window:

    sp_start_job '<Your Job Name here>'

     

     


     

    Jagannathan Santhanam


    • Edited by jsanthanam Friday, January 13, 2012 9:00 PM
    Friday, January 13, 2012 8:51 PM
  • Sorry for not being clear.. The bat file is executed through script task in SSIS package. Yes I have included pause in the bat file. So i am not using "Operating system (CmdExec)" from Agent job.
    Gopal
    Friday, January 13, 2012 9:09 PM
  • Can you go to your job step and in the advanced tab, enable the output of the execution to a text file?
    Jagannathan Santhanam
    Friday, January 13, 2012 10:10 PM
  • Hi... Did that and here is the content of that file

     

    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 64-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.

    Started:  2:17:25 PM
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started:  2:17:25 PM
    Finished: 2:17:26 PM
    Elapsed:  0.344 seconds


    Gopal
    Friday, January 13, 2012 10:17 PM
  • Go to the General tab of the job step and in there select the "Command Line" tab and copy the command line. OPen a DOS Command window and

     

    type DTEXEC <<paste the command you copied from the above step>>

     

    and execute the above in the dos command prompt.


    Jagannathan Santhanam
    Friday, January 13, 2012 10:23 PM
  • Hi Jagannathan!! thank for ur patience..!!

     

    I couldnt follow your direction from the previous post. Can you explain it? I am confused on the command part.. is this command should be copied from bat file?

     

    Thanks,

    Gopu


    Gopal
    Friday, January 13, 2012 10:48 PM
  • Open the SQL job and open the specific step that contains this SSIS package execution. Click on the "Command line" tab and you should see syntax such as:

    /SQL "<<Package Name>>" /SERVER "(local)"  /CHECKPOINTING OFF /REPORTING E

    Copy the above command, open a DOS prompt and add DTEXEC to the beginning of the above command and run it in the DOS prompt. What do you see there? Hope this helps. Also, I am not sure what the problem may be here and I am taking a wild guess that it might be something to do with file system permissions.


    Jagannathan Santhanam
    Monday, January 16, 2012 2:49 PM
  • Here is what I get

    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 64-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.

    Started:  7:00:18 AM
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started:  7:00:18 AM
    Finished: 7:00:18 AM
    Elapsed:  0.172 seconds

    While it executes the package it opens up and closes one more command prompt. And the package runs successfully. Also it moves the file successfully


    Gopal
    Monday, January 16, 2012 3:08 PM
  • This tells me that the user id your SQL agent uses does not have permissions to either access your local or remote file system. What do you use for your SQL Agent? A domain userid or a localuserid?
    Jagannathan Santhanam
    Monday, January 16, 2012 4:03 PM
  • It just says Run as Sql Server Service account. How can I find that out?


    Gopal
    Monday, January 16, 2012 4:10 PM
  • You need to set a proxy which is a domain account based one to execute the package step in the Agent as described here:

    http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx


    Arthur My Blog
    • Marked as answer by Gopal425 Monday, January 16, 2012 5:26 PM
    • Unmarked as answer by Gopal425 Monday, January 16, 2012 5:33 PM
    • Marked as answer by Gopal425 Tuesday, January 17, 2012 10:47 PM
    Monday, January 16, 2012 4:20 PM
  • Go to the services and double-click on "SQL Server Agent ....." and click on the "Log On" tab. You should be using a Domain account as Arthur rightly mentioned. A "Local System Account" will run your job / package but will not have access to copy files over your network to another network share.


    Jagannathan Santhanam
    Monday, January 16, 2012 5:03 PM
  • Creating proxy account worked!! Before I can happy I landed into another snag again. When I run the job first time, Runs perfectly fine, But if I try to run it again it starts executing and sits there without doing anything. I have to restart Agent to stop the job. So I tried to schedule the job to run every minute. Again it ran successfully the first time. But at its second iteration it sits there saying executing and does nothing..

    Thank you for the suggestion. Atleast I have got some where now.

     

    Gopu


    Gopal
    Monday, January 16, 2012 5:24 PM
  • Thx for you patience again!! Yes Arthur was right.. But in another problem right now!! check my previous post!!

    Gopal
    • Edited by Gopal425 Monday, January 16, 2012 5:26 PM
    Monday, January 16, 2012 5:26 PM
  • Do you still have the "pause" command in your .BAT file? Not sure why it would run the 1st time and not subsequently. Also, could you mark posts as "Propose as answer" once you are done?


    Jagannathan Santhanam
    Monday, January 16, 2012 6:52 PM
  • Now I think the issue is there is an error that you do not see. Perhaps gets generated by the BAT file and piped into the DOS window, I actually wanted to ask how do you copy all that to Linux. Typically we just FTP files out to non Win machines. Perhaps you used Samba to create a mixed network. Not sure. Anyways, to remedy add as much logging to file(s) as possible, this way will find out where it glitches.
    Arthur My Blog
    Monday, January 16, 2012 7:07 PM
  • Everything works fine every time when its executed manually. Only when its scheduled it does that. but anyways I am trying to schedule a Windows task to perform this operation. Hopefully that would work.

     

    Thanks,

    Gopu


    Gopal
    Monday, January 16, 2012 10:00 PM
  • I though only the 1st time you run it in Agent it works, but the 2nd does not.

    Then the issue is with the account [rights] you are using to run the package with.

    It is advicated to run the package step using a domain proxy.


    Arthur My Blog
    • Proposed as answer by Eileen Zhao Tuesday, January 17, 2012 12:48 PM
    Tuesday, January 17, 2012 3:41 AM