Monday, July 29, 2013

Run SSIS ETL from SQL

SET @SQLQuery= 'DTEXEC /ISSERVER "\"\SSISDB\TWC\TWC_ABAQAS\'+@ETLName+'.dtsx\"" /SERVER "\".\"" /X86 /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /Par "\"$Package::sProcessName(String)\"";'+@ProcessName+' /Par "\"$Package::sJobName(String)\"";'+@JobName+' /CALLERINFO SQLAGENT /REPORTING E'

EXECUTE @returncode = master .. xp_cmdshell @SQLQuery,NO_OUTPUT