SSIS Data Flow Scripting

I am very much excited to post my first BI article on my blog.

Microsoft SSIS is a powerful ETL technology to work on both OLTP and OLAP processes with its rich set of API components like control flow components, data flow components like various types of sources, destinations and transformation.

Using Data flow scripting transformation, we can use .NET code to customize the functionality of SSIS.
To know more about this transformation one should have knowledge of both SSIS and .NET.

For more information,  go through this video posted on Channel9 web site. For starters, Channel9 is a microsoft maintained web site with lots of videos, blogs, shows and series etc.

I hope this information is useful to the relevant audience.

Reference: DP (http://dptechnicalblog.blogspot.com/ )

SQL Server: Difference between Primary and Foreign Keys

Primary Keys:
Uniquely identify a row in a table with one or more columns
Column values cannot be null
There can only be one primary key on a table
Candidate for either a clustered or non-clustered index to improve performance
Additional information - Finding primary keys and missing primary keys in SQL Server
Foreign Keys:
They physically define the relationship between tables by preventing data integrity issues in your database (e.g. The database prevents line items from being created without an existing order header).
They logically document the relationships between tables by showing how all data relates to each other. To someone new to your organization, this allows him/her to get a good understanding of how the business works (e.g. Every order taken must have a valid customer assigned).
Foreign Keys are native to SQL Server and are designed to prevent data integrity issues. Business logic developers should not be in the business of verifying table relationships.
If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans.
Reference: DP(http://dptechnicalblog.blogspot.com )

Extracting Only Numbers from a String

While working on projects, sometimes we may need to exatract only numbers from a string.

The regular approach is to run a while loop on given string to check each and every character and extract it to get the result.

Here is alternative approach I read in another article,

Declare @str varchar(100),@result_num varchar(100)
set @str='zr3jgc124785ce64'
set @result_num=''
select @result_num = @result_num+
case when number like '[0-9]' then number else '' end from
(select substring(@str,number,1) as number from
(select number from master..spt_values
where type='p' and number between 1 and len(@str)) as t
) as t
select @result_num as [only numbers]
go

Result:
312478564

Reference : DP (http://dptechnicalblog.blogspot.com/ )

SQL SERVER: User Defined Data Types

To create user defined data types, use either of the 2 methods.

1. Using T-SQL syntax

CREATE TYPE [Schema].[Datatype Name] FROM Datatype [NULL / NOT NULL]

E.g.
USE [AdventureWorks2008]
GO
/****** Object: UserDefinedDataType [dbo].[Test] ******/
CREATE TYPE  [dbo].[Test]  FROM  [int]  NOT NULL
GO

2. Using SSMS also we can acheive this.

In database expand the Programmability section, the Type section and the User Defined Data Type section
Right click and select New User-defined Data Type and a pop up appears as below
Fill appropriate data for Schema, Name and Data type fields and save it.

Reference : DP (http://dptechnicalblog.blogspot.com/ )

SQL Server : Challenges of Working with Nothing

If your data contains NULL values there will be deviations in result set, so the developer should be very careful while dealing with NULL values data.

Usually when comparing a column's data to a value or comparing column's data it results either in TRUE or FALSE, but comparision with NULL returns UNKNOWN which in somemeans same as FALSE.
We don't find the difference with '=' operator but with remaining operators like '<>' , the results will vary significantly as shown below.

USE AdventureWorks2008
GO
--Returns 504 rows
SELECT ProductID,Name
FROM Production.Product
GO
--Returns 248 rows
SELECT ProductID,Name
FROM Production.Product
WHERE Color IS NULL
GO
--Expected 478 rows but returns 230 rows
SELECT ProductID,Name,Style,Size,Color
FROM Production.Product
WHERE Color <> 'Blue'
GO
--Returns 478 rows
SELECT ProductID,Name,Style,Size,Color
FROM Production.Product
WHERE Color <> 'Blue' OR Color IS NULL
GO

We can observe the significance in result set due to NULL in last 2 query results.

Reference: DP (http://dptechnicalblog.blogspot.com/ )