Using SQLCMD within SSIS package

• TeraByteMe

  SSCarpal Tunnel

  Points: 4171

  More actions

  November 13, 2009 at 6:00 pm

  #212292

  I'm trying to work out a way to use SQLCMD to run a backup script through an SSIS package. I think I have determined that you need to include a Execute Process Task but to call SQLCMD.exe but not sure what is the next steps needed? Would I need to add and link an Execute SQL Task that includes the T-SQL code? The purpose of the script is to create a backup of a database on one SQL server instance, copy that to a share location, and then from that share location restore the database to a different SQL server instance.

  Appreciate any help you can provide.

  Thanks

  A clever person solves a problem. A wise person avoids it. ~ Einstein
  select cast (0x5365616E204465596F756E67 as varchar(128))

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  November 13, 2009 at 11:16 pm

  #1079323

  SQLCMD would be used to execute a sql script. Why wouldn't you just use an Execute SQL task? It is right there just for this reason..

  CEWII

• rx2526

  SSC Journeyman

  Points: 92

  More actions

  March 26, 2013 at 9:55 pm

  #1601019

  Background: I have .sql script file of a sql job that incudes a powershell task step. This powershell job step uses sql agent tokens like this -

  $sqlInstance = "(ESCAPE_DQUOTE(SRVR))"

  $sql_conn = New-Object system.Data.SqlClient.SqlConnection

  $sql_conn.ConnectionString = "Data Source=$sqlInstance;Integrated Security=true;Initial Catalog=mydb;"

  $sql_conn.Open()

  $cmd = new-Object System.Data.SqlClient.SqlCommand

  I developed an ssis package using Executescript task to run it in a loop across multiple sql instances to deploy the job. SSIS has a bug when the powerhsell script step uses sql agent tokens. More on that bug in a different post. But for now I need to find a workaround.

  My question: I have need to invoke ExecuteProcess task in SSIS as a workaround like this - sqlcmd -S myServer\instanceName -i C:\myScript.sql

  How do I pass the parameters _s and -i

  I am using SQL Server 2008 R2 Enterprise Edition on Windows 2008 X64 server

• rx2526

  SSC Journeyman

  Points: 92

  More actions

  March 26, 2013 at 11:11 pm

  #1601026

  Please disregard. I figured it out. For anyone having a same issue the solution is below:

  For example I am invoking sqcmd as below

  sqlcmd -S myServer\instanceName -i "\\myserver\my path\myScript.sql"

  In the execute process atsk general tab , set Executable property to : C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcomd.exe

  in Expressions tab, select Arguments and type in below expression

  "-S " + @[User::InstanceName] + " -i " + " \"" + @[User::FilePath] + "\""

  where InstanceName and FilePath are my package variables set to the sql instance and teh path to the sql script.

  Since my script path has spaces I put the " around the file name. The escape charcter for SSIS expressions is \" for quote.