View data_tap_execution.sql
USE SSISDB | |
GO | |
/* | |
Testing SSIS data taps | |
Output directory: "C:\Program Files\Microsoft SQL Server\130\DTS\DataDumps" | |
*/ | |
-- Create the execution instance |
View exec_status.sql
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]')) |
View conditional_file.cs
public void Main() | |
{ | |
// Create a logical file object | |
System.IO.FileInfo theFile = new System.IO.FileInfo(Dts.Variables["Filename"].Value.ToString()); | |
// If the update date on the file is greater than the date specified in the MinDateStamp | |
// variable, set the variable flag to process the file. | |
if (theFile.Exists | |
&& theFile.LastWriteTime > DateTime.Parse(Dts.Variables["MinDateStamp"].Value.ToString())) | |
{ |
View validate.sql
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 |
View ssis_zip_step1.cs
// 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 |
View ssis_zip_step2.cs
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; |
View ssis_zip_step3.cs
// 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; |
View ssis_zip_step4.cs
Dts.TaskResult = (int)ScriptResults.Success; |
View ssis_unzip_step1.cs
// 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 |
View log_level.sql
-- 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 | |
/* |
OlderNewer