tdmitch

/*
	Script name: Purge SSIS Catalog log tables
	Author: Tim Mitchell (www.TimMitchell.net)
	Date: 12/19/2018

	Purpose: This script will remove most of the operational information from the SSIS catalog. The 
				internal.operations and internal.executions tables, as well as their dependencies, 
				will be purged of all data with an operation created_time value older than the number
				of days specified in the RETENTION_WINDOW setting of the SSIS catalog.
				

tdmitch

CREATE OR ALTER PROCEDURE [dbo].[GetGenericReportData]
(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)	
)

AS

BEGIN

tdmitch

=Parameters!SchemaName.Value & "." & Parameters!TableName.Value

tdmitch

=(Max(Fields!ID.Value, "ReportSchema") < 2)

tdmitch

=LOOKUP("Column001", Fields!ColumnID.Value, Fields!ColumnName.Value, "ReportSchema")

tdmitch

CREATE OR ALTER PROC [dbo].[GetGenericReportMetadata]
(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)
)

AS

SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) [ID]
	, 'Column' + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY c.column_id) AS VARCHAR(10)), 3) [ColumnID] 

tdmitch

public void Main()
{
    // Create a logical file object 
    System.IO.FileInfo theFile = new System.IO.FileInfo(Dts.Variables["vFilename"].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["pMinDateStamp"].Value.ToString()))
    {

tdmitch

ALTER DATABASE [ChangeTracking] SET change_tracking = ON (change_retention = 14 days) 

tdmitch

SELECT *
FROM CHANGETABLE(CHANGES dbo.Emp, 478) 

tdmitch

DELETE dbo.Emp
WHERE  FirstName = 'Joey'
AND LastName = 'Tribbiani'