none
Execute DOS command in SSIS

    Question

  • Hi,

    I want to execute osql command from SSIS. osql is a DOS command.

    I saw posts stating that dos commands can be executed in ssis using Execute process task.

    I know to execute the osql command from dos command prompt.
    Can anyone explain me how to call the command in ssis.

    Thanks....

    Monday, March 16, 2009 7:21 AM

Answers

  • Using a trusted connection:

    osql /E /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"

    Using a secure connection:

    osql /U sa /P password /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"


    -o switch to specify an output destination


    In SSIS:  Use an Execute Process Task > Process > Executable > C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe
                   
                   Arguments >  /E /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"

    Replace arguments as required.

    I hope this helps!                      




     

    • Marked as answer by Sweety P Wednesday, March 18, 2009 12:05 PM
    Tuesday, March 17, 2009 2:12 PM

All replies

  • If the purpose of using the osql (or sqlcmd) command is to conenct to an SQL Server instance, why don't you consider using a Execute SQL Task?

    Thanks.
    Monday, March 16, 2009 7:53 AM
    Answerer
  • I have a stored procedure which consists of 2 Select and many print statements. I need this output in a txt file. so i tried using osql.

    Thanks....
    Monday, March 16, 2009 8:04 AM
  • Why don't you execute the stored procedure using a SQL command in a OLE DB /SQL Server Source.
    To write to a text file, use a Flat File Destination.
    Monday, March 16, 2009 9:28 AM
    Answerer
  • Hi,


    As i told earlier the stored procedure has 2 select statement ie., i will get 2 different result set and many print statements in the middle of the select statements.

    The procedure will out 2 result sets and print statement messages. How to pass these  to a flat file destination.

    Thanks...
    Tuesday, March 17, 2009 4:40 AM
  • Using a trusted connection:

    osql /E /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"

    Using a secure connection:

    osql /U sa /P password /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"


    -o switch to specify an output destination


    In SSIS:  Use an Execute Process Task > Process > Executable > C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe
                   
                   Arguments >  /E /d myDB /S myServer /Q "mySProc" -o "C:\outputfile.txt"

    Replace arguments as required.

    I hope this helps!                      




     

    • Marked as answer by Sweety P Wednesday, March 18, 2009 12:05 PM
    Tuesday, March 17, 2009 2:12 PM
  • Its working fine.
    When i execute the task, it opens the command prompt window suddenly and closes it after finishing execution.

    Is there any other option to avoid this?

    Thanks....
    Wednesday, March 18, 2009 12:39 PM
  • Yes, in the Execute Process Task Properties > Change WindowStyle to 'Hidden'


    Cheers!
    Thursday, March 19, 2009 12:48 PM