GPG File Encryption in SSIS

Using GPG File Encryption in SSIS

Step 1: First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system.

Step 2: Create a new ssis package.

Step 3: Add ExecuteProcessTask. 

Step 4: To encrypt a file, configure Execute Process task as shown below:

Executables: C:\Program Files (x86)\GNU\GnuPG\gpg.exe

Arguments: --yes --always-trust  --recipient Masterworks --output  Filename.GPG --encrypt Filename.txt
WorkingDirectory: D:\File Path....

Step 4A: To decrypt a file, add below code in arguments:

--yes --always-trust --passphrase xxxxxx --output Filename.txt -d Filenname.GPG

Enter your GPG password in place of xxxxxxx .

Scenario - Updating a column based on the other column's value using Dense_Rank function

Requirement:

There should only be one row for any given TransactionId that has a 1 in the ResponseCount. All other rows should have a 0.

In cases where a response is split among several rows, add a 1 to the row based on the transaction type hierarchy below:
1) Donation
2) Subscription
3) Product
4) Event
5) Non Cash
6) Communcation

The top ranking row will get a 1 while the other(s) will get a 0.

In cases with only one row, it will of course get a 1

If there are multiple rows within any of the 1-6 categories above, select the highest dollar row first to get the ResponseCount 1 value, else select the first row.

Solution:

Create a table "dbo.TransactionDetailGiftTest" as shown below:


CREATE TABLE [dbo].[TransactionDetailGiftTest](
[TransactionHistoryKey] [int] NOT NULL,
[DSTransactionId] [int] NOT NULL,
[Type] [varchar](20) NOT NULL,
[TransactionDate] [date] NULL,
[FiscalYear] [int] NULL,
[FiscalMonth] [int] NULL,
[TotalAmount] [money] NULL,
[ResponseCount] [int] NULL,
 CONSTRAINT [PK_TransactionDetailGiftTest] PRIMARY KEY CLUSTERED
(
[TransactionHistoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Also create a Dimenstion table "dbo.dim" for type hierarchy 

CREATE TABLE [dbo].[Dim](

[ResponseCountKey] [int] IDENTITY(1,1) NOT NULL,

[Type] [varchar](20) NOT NULL,

 CONSTRAINT [PK_Dim] PRIMARY KEY CLUSTERED 

(

[ResponseCountKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now write the below mention script in order to achieve the result

WITH CTE AS (SELECT Responsecount, DENSE_RANK()

OVER (PARTITION BY dstransactionid ORDER BY td.responsecountkey, totalamount DESC) AS Result

FROM dbo.TransactionDetailGiftTest tt

INNER JOIN dbo.dim td on tt.[Type] = td.[type])

UPDATE CTE

SET ResponseCount = Result

UPDATE dbo.TransactionDetailGiftTest

SET ResponseCount = 0

WHERE responsecount > 1 

Now the table should be updated as required: Run the query to check the table:

SELECT * FROM dbo.TransactionDetailGiftTest

This scenario helps to understand a logic for the similar kind of requirement.

Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.