November 13, 2009 at 6:00 pm
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))
November 13, 2009 at 11:16 pm
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
March 26, 2013 at 9:55 pm
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
March 26, 2013 at 11:11 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply