Sunday 25 March 2018

Use of buffer in SSIS

A buffer is a delimited memory zone used by SSIS to hold the data that it is dealing with during the execution of a package. Its metadata is invariable once the design is complete. In other words, its metadata (fields, data types, etc) cannot be changed at execution time. However, its content can be. The process of manipulating the data inside the buffers is the job of SSIS transformations.
Since SSIS buffer addresses are fixed in memory, using them is extremely fast. There is no need to either copy information between buffers or to re-allocate the buffers themselves.

Use of buffers by SSIS transformation type

 Row-by-row transformations

Rows are processed as they enter the component, thus, there is no need to accumulate data. Because it is able to use buffers previously created (by preceding components/precedents), it’s not necessary to create new ones and copy data into them. Examples:  Data Conversion, Lookup, Derived Column, etc.
Partially blocking transformations
 These are usually used to combine data sets. Since there is more than one data entry, it is possible to have huge amounts of rows waiting, stored in memory, for the other data set to reach the component. In these cases, the component’s data output is copied to new buffers and new execution threads may be created. Examples: Union All, Merge Join, etc.

Fully blocking transformations

Some transformations need the complete data set before they start running. Therefore, these are the ones that impact on performance the most. In these cases, as well, new buffers and new
execution threads are created. Examples: Aggregate, Sort. SSIS reuses previously used buffers as much as possible, in order to increase performance. Row-by-row transformations are known as synchronous. Each input row produces one output row. On the other hand, in partially-blocking and fully-blocking transformations, known as asynchronous, there is no need to have the same number of input rows as output rows (they need no output rows at all).

Buffer sizes

Buffer size is a property, and it is defined by the SSIS engine, which in all cases will try to increase performance, though this will depend on the values of certain configurable properties and on its internal limits:
DefaultMaxBufferRows:
 This is a dataflow property. It is configurable, although SSIS defines it by default at 10,000. SSIS multiplies Estimated Row Size times DefaultMaxBufferRows, to estimate how big a buffer would be.


DefaultMaxBufferSize:

 Again, a dataflow property. It is fixed to 10MB by default. It is possible to increase it trying to improve performance, but there’s an upper threshold of 100MB, known as MaxBufferSize, which cannot be changed.

Execution tree in SSIS

At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread.

Execution trees are enormously valuable in understanding buffer usage. They can be displayed for packages by turning on package logging for the Data Flow task. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation.

View SSIS Variables via Message Box

We need to see the current value of a variable in SSIS. There are several ways to accomplish this, but my favorite is the Message Box. We can use either VB or C# to display the value of a variable.
Let’s see
Here I take the expression task

I am taking a variable in that I store the expression value, which we want to display via a Message box.

Double click on the expression task.

Click ok.
Now I am taking the script task.

Double click on this task.

Click on Edit Script. Here I am selecting C# editor.

Save it.
Now I am running this package.


Get Decimal number to Roman using T-SQL script

I have a requirement to display the decimal number to Roman number in my report.
For example, the number is 5 –V, 56 –LVI
Roman number:
This number system is based on below seven symbols


Creating function using the script.
CREATE Function dbo.Get_Roman_Number(@N as varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @s varchar(100), @r varchar(100),
          @i bigint, @p int, @d bigint
  SET @s = ''
  SET @r = 'IVXLCDM' -- Roman Symbols
  /* There is no roman symbol for 0, but I don't want to return an empty string */
  IF @n=0
     SET @s = '0'
  ELSE
  BEGIN
     SELECT @p = 1, @i = ABS(@n)
     WHILE(@p<=5)
     BEGIN
       SET @d = @i % 10
       SET @i = @i / 10
       SELECT @s = CASE
         WHEN @d IN (0,1,2,3) THEN
           Replicate(SubString(@r,@p,1),@d) + @s
         WHEN @d IN (4) THEN
           SubString(@r,@p,2) + @s
         WHEN @d IN (5,6,7,8) THEN
           SubString(@r,@p+1,1) +
           Replicate(SubString(@r,@p,1),@d-5) + @s
         WHEN @d IN (9) THEN
           SubString(@r,@p,1) + SubString(@r,@p+2,1) + @s
         END
       SET @p = @p + 2
     END
     SET @s = Replicate('M',@i) + @s
     IF @n < 0
     SET @s = '-' + @s
   END
   RETURN @s
END
GO
Function created successfully.

See the example


See another example.

See in the Roman table


Convert first character of each word in UPPER case in sql server

Recently I worked on the report. My requirement is to display the customer name first character in upper case. SQL Server doesn't provide any built-in function for this requirement. In this article, I am creating T-SQL function. This function after every space, first later will be in UPPER case.
Example:
Input: BAGESH KUMAR SINGH
The output will be: Bagesh Kumar Singh
Below is the function
CREATE FUNCTION [dbo].[Convert_Upper_Case_First_Char]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END

See below.


Function created successfully.
Let’s use this function.




Get Month End Day and Date in sql server

Using sql script we will get the month day and date of any date.
SELECT datename(dw,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1) AS [Month End Day],
DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS [Month End Date]

See the output

Today date is 03/18/2018

Month-end day and date is below

If you want to get any particular date month you need to write the date in place of getdate() see below.

See it calendar

We will get the expected result using EOMONTH date function. See below
SELECT datename(dw,EOMONTH(GETDATE())) AS [Month End Day] ,
EOMONTH(GETDATE()) AS [Month End Date]


See the output below




Loading MS Access database to sql server table using SSIS

In this post, we will learn how to load the MS access database table to SQL server or other destination using SSIS.
Taking the data flow task.

Double click on the data flow task.
Here I am taking source as OLEDB source.

Now we need to create a connection manager.
Creating a new connection.

Select Provider as Microsoft office 12.0 Access Database Engine OLEDB Provider
Click ok.


Write the MS Access Database path.
Click on the test connection.

 Click ok.
We will get the list to tables which are in the Access database.
We will see the column

Click ok.
Now I am taking destination as OLEDB.
Map the source and destination column

Click ok.
Now the package is ready to run.
Before running this package records in the table.


 MS Access records

 Now I am running this package.
Package executed successfully
.
See the records in the table.


Saturday 24 March 2018

Loading SAS Dataset in a table using SSIS

One of my client’s vendor is giving the SAS data. We need to load that data into repo table.
In this post, we will see how to load the SAS dataset into the repo table.

First of all, we need to download and install the SAS Providers for OLE DB. There are multiple versions make sure to download the correct version. We only need the select SAS Providers for OLE DB.

Before downloading the provider we need to do the registration on this site.

Select the version of the provider and download it.
Once we download this e need to install this provider.

For the demo, I have downloaded some SAS data set from the below site.

Now let ‘s see how to lading the SAS dataset.
Open the SSDT.
Taking Data flow task.

Double click on the data flow task.
In data flow pan I am taking OLEDB source.

Here we need to create connection manager. Double click on the OLEDB Source.

Here we need to select the provider as SAS Local Data Provider.
Click ok.
                         
Here we need provide the file location where the SAS dataset is stored.
If that folder is password protected we need to provide the password. Otherwise, we need to keep it blank.
Click on the test connection.

Click ok.
Select the Data access mode as table or view.
                        
In the drop down we will get the all SAS dataset list which are stored on that folder. We need to select the SAS set file which we want to load.

Now select Column.

Click ok.
Now data is loaded in the source.
If we to do any transformation we can do it.
Here I am taking the multicast transformation and enabling the data view to see the records.

Now I am running this package.

We can use destination to load the data.


Popular Posts