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.