SSIS - Script task export import variable

  public void Main()

        {

            // TODO: Add your code here

            string str = Dts.Variables["User::Internal_Sales_File_Name"].Value.ToString();

            str = str.Substring(str.Length - 9, 4);

            Dts.Variables["Internal_Sales_Year"].Value = str;

           // string test = Dts.Variables["User::Internal_Sales_Year"].Value.ToString();

       

            Dts.TaskResult = (int)ScriptResults.Success;

        }

    }

}

String matching with data in SQL server using COLLATE

String matching with data in SQL server using COLLATE

update CLEAN.Product_Ref set laboratories_Clean = UPPER(laboratories_Clean)
where LOCAL_PACK_ID in 
(select LOCAL_PACK_ID from CLEAN.Product_Ref
where laboratories_Clean != UPPER(laboratories_Clean) collate Latin1_General_CS_AI)                                                                                                                                              

HOW TO CHECK IF STRING CONTAINS LOWER CASE LETTER OR UPPER CASE LETTER USING T-SQL

Use the below SQL query:-

select distinct laboratories_Clean from CLEAN.Product_Ref

where laboratories_Clean != UPPER(laboratories_Clean) collate Latin1_General_CS_AI

Populating Slowly Changing Dimensions with SQL Server 2008 MERGE statement

Populating Slowly Changing Dimensions with SQL Server 2008 MERGE statement

MERGE statement

MERGE is a new feature in SQL Server 2008 that provides an efficient way to perform multiple DML operations. Using MERGE statement INSERT, UPDATE or DELETE can be performed on a target table based on the result of a join with a source table.

The MERGE statement has three WHEN clause, each performs a specific DML operation on a given row in the result set

WHEN MATCHED allows you to UPDATE or DELETE the given row in the target table for every row that exists in both source and target table

WHEN NOT MATCHED [BY TARGET] allows you to INSERT a row into the target table for every row that exists in the source table but not in the target

WHEN NOT MATCHED BY SOURCE allows you to UPDATE or DELETE the given row in the target table for every row that exists in target table but not in source table

You can also specify a search condition with each of the WHEN clause to choose the DML operation to be performed on the selected row

OUTPUT clause of the MERGE statement includes a virtual column $action which is used to identify the DML action performed in each row of the result set

Slowly Changing Dimensions

The data in the Slowly Changing Dimensions (SCD) changes periodically. The SCD may be a customer dimension, whereas any new customers can be added or the contact information of the existing customers may be changed.

Earlier this type of dimensions can be handled using multiple DML statements. With MERGE statement in SQL Server 2008 populating SCD can be achieved using a single statement.

Let us take DimCustomer as a dimension table which tracks the customer contact information (phone) for each customer of a particular retail store. Every day thecustomers table in the transactional system is updated with changes to the existing customer or inserting a new customer record. At the end of each month the changes in the customer table are to be applied in the DimCustomer table. A row has to be inserted for each new customer. Existing customers whose contact information changed need to be updated with IsCurrent as 0 and a new record for the existing customer has to be inserted with current contact information.

CustomerKey	CustomerName	Phone	IsCurrent
5011	John Smith	5650 578 632	1
5012	Patrick Brown	7451 241 584	1

Table 1: DimCustomer

CustomerKey	CustomerName	Phone
5011	John Smith	8845 001 863
5013	Jimmy	7554 846 563

Table 2: Customer

 The following Transact-SQL statement updates the Phone for the customer John Smith and adds a new record for Jimmy in the DimCustomer table.

 INSERT INTO DimCustomer(CustomerKey, CustomerName, Phone, IsCurrent)

  SELECT CustomerKey, CustomerName, Phone, 1

  FROM

    (

        MERGE DimCustomer as trgCustomerDim

        USING Customer AS CustSrc

        ON (trgCustomerDim. CustomerKey = CustSrc. CustomerKey and trgCustomerDim.IsCurrent = 1)

        WHEN MATCHED THEN

            UPDATE SET trgCustomerDim.IsCurrent = 0

        WHEN NOT MATCHED THEN

            INSERT VALUES (CustSrc. CustomerKey, CustSrc. CustomerName, CustSrc. Phone, 1)

        OUTPUT $action, CustSrc. CustomerKey, CustSrc. CustomerName, CustSrc. Phone

    ) AS Changes(action, CustomerKey, CustomerName, Phone)

  WHERE action = ‘UPDATE’;

The output of MERGE statement is

$action	CustomerKey	CustomerName	Phone
UPDATE	5011	John Smith	8845 001 863
INSERT	5013	Jimmy	7554 846 563

The OUTPUT of the MERGE statement is filtered with the action ‘Update’ to insert the new record for the changes in existing customer details. This will make the current information as available and also maintains the old information of the customer.

 The DimCustomer table after processing the MERGE statement would be like this

CustomerKey	CustomerName	Phone	IsCurrent
5011	John Smith	5650 578 632	0
5012	Patrick Brown	7451 241 584	1
5013	Jimmy	7554 846 563	1
5011	John Smith	8845 001 863	1

SQL Server 2008 INDEX

SQL Server Indexing features - 

SQL server gave us the feature of indexing for speeding up the performance in how we retrieve data through queries. Imagine we have a book with many chapters, if we are required to turn pages and find a particular chapter of our interest it would take more time.

Indexing in a book gives a flexibility to look-up the page number of the book and directly go to that page number to find our chapter of interest and hence in a way more quick.

Similarly we have indexing in SQL server to retrieve data based a key value.

Types of indexes in SQL server

• Clustered
• Non-clustered
• Unique
• Filtered
• XML
• Full text
• Spatial
• Column store
• Index with included columns
• Index on computed columns

Today we will be discussing about Clustered, Non-Clustered and Unique indexes.

Clustered Index - 

It determines the way in which the data is physically ordered in a table. A table can have only one clustered index.

To understand this better let us create a table as shown below :-

Create table tblEmployee

(

[EmpID] int primary key,

[EmpName] varchar(20),

[Gender] char(1),

[EmpSalary] int

)

After creation of the table open "Object Explorer", expand tables in the database where the table tblEmployee was created expand "Key" and "Indexes", you can see the creation of primary key and an Index.

So we can now conclude for the above example that creation of primary key creates clustered index by default. Please see below screenshot for reference.

So whats actually the use of this clustered index creation!!!

As we stated above that indexing determines the way data is physically stored in a database, in the below example we will see how it stores the data.

In the creation of the table tblEmployee we have used EmpID as the primary key and the clustered index is created on this column. Lets try to insert values in the table tblEmployee.

Insert into tblEmployee values(3,'Indranil','M',25000)

Insert into tblEmployee values(1,'Vipul','M',35000)

Insert into tblEmployee values(5,'Deepika','F',18000)

Insert into tblEmployee values(4,'Mukul','M',19000)

Insert into tblEmployee values(2,'Shikha','F',21000)

We can see that while insertion of values in the table, the order of the EmpID is not inserted orderly, but as we have the clustered index on the EmpID listed, when we view the data through select statement, the data are automatically ordered in ascending order of EmpID. So the clustered index on EmpID helped the data get stored in accordance to ascending order of EmpID.

select * from tblEmployee