SSIS: Perform upsert (Update/Insert) using SSIS Package

It is a common scenario, when we need a script, package or something else to perform insert and update in a table simultaneously. Sometimes the task seems easy and sometimes it takes so much time to perfectly synchronize the databases. In this blog post we will try to create a SSIS package that performs the synchronization task by performing upsert (Insert/Update) using SSIS lookup transformation.

Traditional (Row-Oriented) Database Vs Columnar Database

What is the real difference of columnar database over traditional (row-oriented) database? 

Columnar database is a database system that stores data in a column-wise fashion while in traditional database data has stored in row-wise fashion. Let’s see an example so that we can visualize how data will be stored for these two approaches. Below image shows our example record set.

Redshift: How to alter column datatype in redshift

There is no direct way to change the column datatype in redshift. If we want to change the column name we can use redshift alter statement with rename keyword like,

alter table BDPlayers rename column category to grade;

Redshift: Store and Querying JSON object in Redshift

Problem: How to store JSON objects in redshift and also apply filter, group by, order by etc on that object?

Solution: Suppose we have a sparse table, where we need to add many columns and it could be exceed the maximum column limit supported by redshift. In that situation we can store the data for a row in key:value pairs in a single JSON string. JSON uses UTF-8 encoded text string so we can store JSON string in redshift text or varchar type column. For demonstration purpose we have created a table and insert some dummy JSON object. Below is the script.

Redshift: Some Interesting behaviour of Redshift Query

I have found some interesting behaviour of redshift query. It is interesting in a sense that the query runs perfectly in redshift but not in other database (SQL Server or PostgreSQL). Let’s see the behaviour. First one describes in http://www.sqlhaven.com/interesting-aggregate-query-result-using-clause-redshift/ this blog post by brother Khorshed Alam.

SSIS: Excel Import 255 character Truncation issue and Solution

Few days ago we have created a SSIS package to load excel data in our SQL Server database. When we run the package for a sample excel file, it runs perfectly. But when we run the package for original excel file it fails.

How to Create, Deploy and Configure SSIS Package: Part 1

Data loading from various source is a common task for data centric application. SSIS package is a very handy and easy to learn tools to load data from various source like excel, flat file or other database. In this SQL Server Integration Service(SSIS) series we try to learn, how to create, deploy, configure and monitor SSIS package. In first article of the series we will create a simple SSIS package which extract data from excel file then load the data to a SQL server database.

Initial Setup

Before we do anything else, we need to create necessary table to which we want to load data from the excel file. Lets create the.

CREATE TABLE DataLoadTest

(

       Id int IDENTITY(1,1) PRIMARY KEY,

       Name nvarchar(500) NULL,

       FirstColumn nvarchar(500) NULL,

       SecondColumn nvarchar(500) NULL,

       GroupOrder int NULL

)

GO