tdmitch

USE SSISDB
GO

/* 
	Testing SSIS data taps

	Output directory: "C:\Program Files\Microsoft SQL Server\130\DTS\DataDumps"
*/

-- Create the execution instance

tdmitch

USE SSISDB
GO

-- Create [toolbelt] schema if it does not yet exist
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'toolbelt')
	EXEC sp_executesql N'CREATE SCHEMA [toolbelt]'
GO

-- Drop table if exists
IF EXISTS (SELECT 1 FROM sys.tables WHERE OBJECT_ID = OBJECT_ID('[toolbelt].[Exec_Status]'))

tdmitch

EXEC [SSISDB].[catalog].[validate_package] 
	@package_name = N'050 Runtime Validation.dtsx'
	, @folder_name = N'TestingSSISPackages'
	, @project_name = N'Testing SSIS Packages'
	, @use32bitruntime = False
	, @environment_scope = D
	, @reference_id = NULL
	, @validation_id = @validation_id OUTPUT

tdmitch

// Store the SSIS variables in local C# variables
string exeName = Dts.Variables["$Package::pPathTo7zip"].Value.ToString();
string src = Dts.Variables["$Package::pSource"].Value.ToString();
string dst = Dts.Variables["$Package::pDest"].Value.ToString();

// Create args string
string args = " -y";                                // Answer Y to any prompt
args += " a";                                       // Create archive
args += " " + "\"" + dst + "\" \"" + src + "\"";    // Source and dest

tdmitch

try
{
    // Create new ProcessStartInfo with vars from above
    ProcessStartInfo ps = new ProcessStartInfo();
    ps.FileName = exeName;
    ps.Arguments = args;
    ps.UseShellExecute = false;
    ps.RedirectStandardError = true;
    ps.RedirectStandardOutput = true;
    ps.CreateNoWindow = true;

tdmitch

// If error, write to log and fail
if (
        // Error conditions:
        (!(Dts.Variables["vExitCode"].Value.ToString() == "0"))         // If exit code = 0
        || exceptionTxt.Length > 0                                      // Or an exception was captured above
        || Dts.Variables["vStdError"].Value.ToString().Length > 0       // Or StdError was captured above
    )
{
    string err1 = Dts.Variables["vStdError"].Value.ToString();
    string err2 = exceptionTxt;

tdmitch

Dts.TaskResult = (int)ScriptResults.Success;

tdmitch

// Store the SSIS variables in local C# variables
string exeName = Dts.Variables["$Package::pPathTo7zip"].Value.ToString();
string src = Dts.Variables["$Package::pSource"].Value.ToString();
string dst = Dts.Variables["$Package::pDest"].Value.ToString();

// Create args string
string args = " -y";                                // Answer Y to any prompt
args += " x";                                       // Extract
args += " " + "\"" + src + "\" -o\"" + dst + "\"";  // Set source and dest

tdmitch

-- Create the execution instance
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
	@package_name = N'30 Parameterized Package.dtsx'
	, @execution_id = @execution_id OUTPUT
	, @folder_name = N'Making the Most of the SSIS Catalog'
	, @project_name = N'Making the Most of the Catalog'
	, @use32bitruntime = False, @reference_id = 2

/*

tdmitch

-- Set up the execution instance
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
	@package_name = N'30 Parameterized Package.dtsx'
	, @execution_id = @execution_id OUTPUT
	, @folder_name = N'Making the Most of the SSIS Catalog'
	, @project_name = N'Making the Most of the Catalog'
	, @use32bitruntime = False
	, @reference_id = 2