Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using SQLCMD within SSIS package Expand / Collapse
Author
Message
Posted Friday, November 13, 2009 6:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 12:52 PM
Points: 158, Visits: 544
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
Post #818940
Posted Friday, November 13, 2009 11:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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
Post #818956
Posted Tuesday, March 26, 2013 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:15 PM
Points: 2, Visits: 18
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
Post #1435755
Posted Tuesday, March 26, 2013 11:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:15 PM
Points: 2, Visits: 18
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.

Post #1435767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse