SSIS datatypes VS SQL Server datatypes

Often it can be extremely confusing when you encounter SSIS data types.  At first glance they seem to be nothing like SQL Server data types.  That's why I wanted to write this blogpost,  below a conversion chart of SSIS data types to SQL Server data types.  This information is readily available on MSDN but it always seems difficult to find.  

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Hope this helps!

Addition to BIDS: BIDS Helper

The Business Intelligence Development Studio (or BIDS) is typically used for developing Microsoft BI software. Now there is room for improvement everywhere and the BIDS Helper offers this improvements.

BIDS Helper is a Visual Studio add-in which will add a number of additional features and options to Visual Studio. Most additions are in the Analysis Services and Integration Services projects and range from comfortable to extremely useful. A lot of these additions are really things that make you wonder why Microsoft didn't include it themselves...

A few examples:
- Integration Services packages can sort by name within a project
- Variables in packages for Scope another move / copy
- Within Analysis Services option to create your attribute relations graphically
- A Smart Diff option packages or differences between two cubes to detect

And there are many more.

The add-in comes from an open source project on Codeplex (from Microsoft) and is free to use. It is available for Visual Studio 2005 as well as 2008, so be sure to choose the right one.

Download BIDS Helper, install and try it yourself!

ShellRunas – Launch Programs with Different Account Credentials

ShellRunas is free utility that integrates into the Windows context menu to launch programs as a different user. It provides similar functionality to that of the command line based RunAs utility. The command-line Runas utility is handy for launching programs under different accounts, but it’s not convenient if you’re a heavy Explorer user. ShellRunas provides functionality similar to that of Runas to launch programs as a different user via a convenient shell context-menu entry.

Screenshot

At BI Architects we are using ShellRunas a lot for logging into SSMS (SQL Server Management Studio) with different credentials. If you want more information about ShellRunas or if you want to download the utility, click here

SSIS Derived Column IF(IIF)

I use this post as a short reminder for myself, because the Derived Column Transformation Editor in SSIS simply does not spell out how to accomplish this.

The following syntax is used in the Derived Column control to place an IF(IIF) statement :
(Boolean Expression ? True Part : False Part)

So, if I were checking against a Trade Type code column (trade_type_cd) to determine whether this column IS NULL or IS NOT NULL, I could simply write up something like this:
ISNULL(trade_type_cd) ? "<Unknown>" : trade_type_cd

Addition to BIDS: Table Difference component

One question arising very often in the Data Warehouse programming is "What are the differences between two tables?" You receive several million customers and should decide what changes need to be done. The standard SSIS toolkit provides SCD component for handling this process, but it is of no practical use when you have to handle more than a couple of thousand records. The Table Difference component doesn't have this limitation.

The component has two inputs: one for "old" and one for "new data". The old and new rows are compared and the result is sent to different outputs:

• Unchanged rows (same in both old and new)
• Deleted rows (appear in old but not in new)
• New rows (appear in new but not in old)
• Updated rows (appear in both old and new, but there is a change)

The diagram below illustrates the component functionality better than thousand words:

Occasionally you may have a requirement to perform different updates, for different columns. The component includes option to tag each input column with identifier. Then for each unique identifier, a separate update output is created and maintained. In this way you can tag with "10" the historical attributes and with "20" the other columns and decide what kind of operation to carry on with the different updates.

The component inputs MUST be sorted and have a collection of fields (keys) that let the component decide when two rows from the inputs represent the same row. This is easily accomplished in SQL with a simple “order by” and a convenient index. Usually slowly changing dimensions maintain an index by the business key, so the sorting requirement is easily accomplished and do not represent a problem.

Click here for more information about the Table Difference component.