Skip to content

Instantly share code, notes, and snippets.

tdmitch

Block or report user

Report or block tdmitch

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
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
/*
You can’t perform that action at this time.