Managing SQL Authentication in SSIS using SQL Server Integration Services Catalogs

Problem Statement: How to manage the password used in SQL Authentication into SSIS Packages?

Solution: We can manage the password using more than one ways as mentioned by following stackoverflow responses:

https://stackoverflow.com/questions/1274080/ssis-connection-manager-not-storing-sql-password

After doing good amount of investigation, I have found that in SQL Server Integration Services Catalogs we can create environment variables by enabling sensitivity to store the password, which is used for login in SQL Authentication. The step-by-step implementation is as follows:

I.           SSIS Package Development:

1.      In Microsoft Data Tool 2012+, create an SSIS Project. In this Project add connection manager to a SQL database using OLEDB connection manager:

Screenshot # 1

As we can see in the above screenshot, I have provided the credentials for SQL Authentication. However, we know that these credentials cannot be stored in SSIS Packages, because the password is sensitive data. Even if we check the check box “Save my password”, but when we reopen the connection manager we do not find the password stored there. To make ourselves clear we can open the source code of the package and there we do not see the password string in the connection manager:

Screenshot # 2

Note: I have currently selected package level property ProtectionLevel=DontSaveSensitive property, so that the password value should not be saved inside the package.

 

2.      Parameterize the connection manager related the following parameters:

a.      Server Name

b.      Database Name

c.      User Name

d.      Password

To store the above values required to parameterize connection, we can create either project parameters or package parameters. The thumb rule is that if a parameter value is going to be used by more than one packages, then that parameter should be created as a project parameter. For this demo purpose, I am creating package parameters:

Screenshot # 3

 

3.      In above screenshot # 2, we can see the entered password string. Now select the Password parameter and press F4 to open its properties window. In the properties window select the Sensitive option to True (by default it's false):

Screenshot # 4

Once we set the Sensitive option to True, the password string will be hidden now:

Screenshot # 5

 

 

II.           SSIS Package Deployment:

1.      Build the SSIS Project, which would create ispac file in \bin\Development location.

2.      In SSMS, go to Integration Services Catalogs and expand it. If you can see SSISDB then it means Catalog is created, but if the SSISDB is not created, then right click to Integration Services Catalogs and click the menu item “Create Catalog…” This will open Create Catalog dialog box, which can be used to create the database.

3.      Once the SSISDB database is created under Integration Services Catalogs, right click on SSISDB, click on “Create Folder…”, and create Demo Folder. Expand the Demo folder and Projects and Environments subfolders can be seen. These subfolders are empty as of now and will be used for the deployment of SSIS Packages and creation of Environment variables respectively.

4.      Deploy the package using the ispac file we created as mentioned in above step # 1. Once the deployment is complete, you may expand the Projects folder under Demo and see the packages which are deployed there (as shown in below screen shot # 4):

 

 

Screenshot # 6

 

5.      Right click on Environments folder and click “Create Environments…” menu item. Create Environment PackageDemo and then create following Environment variables:

Screenshot # 7

 

Note: Make sure to check the Sensitive checkbox. As we have already selected the Password parameter Sensitive property to True and both should match.

6.      Map the environment variables to package parameters: Right click on PackageDemo.dtsx and select Configure… menu item. In the Configure dialog box, for each package parameter, there would be a “…” button, which will open the Set Parameter Value dialog box. If in this dialog box Use Environment Variable option is disabled, then it means the Reference of the SSIS package is not set. Close the Set Parameter Dialog box, select References in Configure dialog box, and set the references.

Screenshot # 8

 

7.      After setting the reference, option “Use Environment Variable” would be enabled as shown in below screenshot # 7:

Screenshot # 9

8.      After mapping of environment variables, right click on deployed package and select Execute… option. In Execute Package dialog box, check the Environment check box and click OK button. You would get the following message:

Screenshot # 10

 

Click on Yes button and the execution report would open. If everything is green like below screenshot, then it means that package have been executed successfully:

Screenshot # 11

Note: Please make sure to set Sensitive property value as True in the package for Password parameter as well as to check the Sensitive checkbox for the Password environment variable.

 

SQL Server Management Studio has limitation of showing 43679 Characters only

In SQL Sever, if we create a table with column varchar(max) or nvarchar(max), then we can store upto 2 GB of data. However when we retrieve such columns, we can only see the data upto 43679 character. If we want to see complete set of data, we can use below script. User needs to provide following three inputs:
1. @StrLenSplit: It should not be more than 43679
2. @ColumnName: Provide the column name with data type as varchar(max) or nvarchar(max)
3. @TableName: Provide table name


--Required input from user
DECLARE @StrLenSplit BIGINT=4000 --should not be more than 43679
DECLARE @ColumnName VARCHAR(256)='jasonstringNvarchar'
DECLARE @TableName VARCHAR(256)='TableJson'

--Variable Declaration Part
DECLARE @stringlen BIGINT, @ExecuteQuery VARCHAR(2000)
DECLARE @ColumnDetails VARCHAR(1000)
DECLARE @idx INT=1, @SplitCount INT, @StringStartIndex INT=1
SELECT @stringlen=MAX(LEN(jasonstringNvarchar)) FROM TableJson

IF(@stringlen>@StrLenSplit)
BEGIN
SET @ColumnDetails=''
SET @SplitCount=1
WHILE (1=1)
BEGIN 

SET @ColumnDetails = @ColumnDetails+' SUBSTRING('+@ColumnName+','+CONVERT(VARCHAR(5),@StringStartIndex)+','+CONVERT(VARCHAR(5),@StrLenSplit)+') AS '+@ColumnName+'_'+CONVERT(VARCHAR(3),@SplitCount)+', '
SET @StringStartIndex=@StrLenSplit+1
SET @StrLenSplit=@StringStartIndex+@StrLenSplit
IF (@StrLenSplit>@stringlen)
BEGIN
SET @StrLenSplit=@stringlen
SET @idx=@idx+1
END
IF(@idx>2)
BEGIN
Break
END
SET @SplitCount=@SplitCount+1
END
END
SET @ColumnDetails = LEFT(@ColumnDetails, LEN(@ColumnDetails) - 1)
SET @ExecuteQuery= 'SELECT '+ @ColumnDetails+' FROM '+@TableName
EXEC( @ExecuteQuery)

Scenario#2: Compressing a file using Script Task in SSIS (BIDS 2008)

Solution: Please add Script Task and write following code inside it.

        Public void Main(string[] args)

        {

             string srcPath = Dts.Variables["zipFiles"].Value.ToString();

            //string srcPath = @"C:\SQL3_Tasks\ssPharmacy\";

            srcPath = srcPath + "\\";

            string FileName = getFileName(srcPath);//"SSPharmacy20150605.txt";

            string JustFileName = FileName.Substring(0, FileName.Length - 4);

            FileInfo fileToCompress = new FileInfo(srcPath + FileName);

            FileStream sourceFile = File.OpenRead(srcPath + FileName);

            FileStream destinationFile = File.Create(srcPath + FileName + ".gz");

            byte[] buffer = new byte[sourceFile.Length];

            sourceFile.Read(buffer, 0, buffer.Length);

            using (GZipStream output = new GZipStream(destinationFile,

                CompressionMode.Compress))

            {

                output.Write(buffer, 0, buffer.Length);

            }

            sourceFile.Close();

           destinationFile.Close();

        }

Detailed Description:

1. DTS Variables: We can add variables into SSIS Package with different scope levels. We can pass any variable to script task. Following are the steps for passing any variable to Task:
2. Edit Script Task, which will open Script Task Editor. Please see below screen shot:
3. There are two sections where we can provide our variables:
1. ReadOnlyVariables: in this section we can add only those variables which we want to provide as input only and which is needed for our functionality implementation in Script Task.
2. ReadWriteVaraibles: In this section we can add those varibales which we want to fill as our functionality output.

Scenario#1: Deleting a particular type of files from a location

Solution: Add SSIS Script Task. Write following code inside it

    public void Main()

        {

          string locZipFiles = Dts.Variables["zipFiles"].Value.ToString();

          DirectoryInfo di = new DirectoryInfo(locZipFiles);

          foreach (string file in Directory.GetFiles(locZipFiles))

          {
             //Will delete all files with .gz extension

              if (Path.GetExtension(file) == ".gz")

              {

                  File.Delete(file);

              }

          }

       }

Above code is deleting all files with extension gZ.