How to execute SQLCMD from SSIS

If you have ever needed to execute SQLCMD from an SSIS package, you can use the Execute SQL Task component along with xp_cmdshell to do the job.

I used the following script inside the Execute SQL Task component to make it work:

DECLARE @Server SYSNAME
DECLARE @SQL NVARCHAR(500)
SET @Server = @@SERVERNAME

SET @SQL = 'EXEC xp_cmdshell ''SQLCMD -S ' + @Server + ' -d <DBNAME> -q "<SomeProc>"'

EXEC sp_executesql @sql