SSIS To SqlServer DataTypes

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

Creating Dimension Table ==> Time (Example)

Script for Dimension table for Time.

Here Date_ID is the primary key which can be used as the foreign key in the fact tables.

CREATE TABLE [dbo].[DATE_DIM]

                (

                 [DATE_ID] [bigint] IDENTITY(1, 1)

                                    NOT NULL

                ,[SQL_DATE] [datetime] NOT NULL

                ,[DAY] [smallint] NOT NULL

                ,[DAY_OF_WEEK] [smallint] NULL

                ,[WEEK] [smallint] NOT NULL

                ,[MONTH] [SMALLINT] NOT NULL

                ,[QUARTER] [SMALLINT] NOT NULL

                ,[YEAR] [SMALLINT] NOT NULL

                ,[DAY_OF_YEAR] [SMALLINT] NOT NULL

                ,[DAY_TEXT] [VARCHAR](50) NOT NULL

                ,[MONTH_TEXT] [VARCHAR](50) NOT NULL

                ,[QUARTER_TEXT] [VARCHAR](50) NOT NULL

                 )

                

 declare @date datetime

 declare @enddate datetime

 set @date = CONVERT(datetime,'01-01-2012')

 set @enddate = CONVERT(datetime,'12-31-2025')

 while @date <= @enddate

   begin

   insert into DATE_DIM

   ([SQL_DATE]

   ,[day]

   ,[DAY_OF_WEEK]

   ,[WEEK]

   ,[MONTH]

   ,[QUARTER]

   ,[YEAR]

   ,[DAY_OF_YEAR]

   ,[DAY_TEXT]

   ,[MONTH_TEXT]

   ,[QUARTER_TEXT]

   )

     select

    @date as sqldate

    ,datepart(D,@date) as [day]

    ,datepart(DW,@date) as [day_of_week]

    ,DATEPART(WK,@date) as [week]

    ,DATEPART (M,@date) as [month]

    ,DATEPART(Q,@date) as [quarter]

    ,DATEPART(DY,@date) as [day_of_year]

    ,DATEPART(Y,@date) as [year]

    ,DATENAME(DW,@date) as day_text

    ,DATENAME(M,@date) as month_text

    ,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT

    set @date = DATEADD(D,1,@date)   

   end                

 

Total Number Of Records In each Table

Quite often in our day today activity we need to check the count of records in each table of the database. So instead of firing count(*) against all the table, we can use the DMV's ie inbuild system procedures or functions to get the desired result.

Code :

select  distinct s.name as Name_Schema,o.name as Table_Name, p.row_count as Total_Records from sys.objects o inner join

sys.dm_db_partition_stats p on o.object_id = p.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type = 'U'

So lets say we want to get the list of tables along with the total records in Adventureworks database.

Below is the output for the same:

 

Name-Schema	Table_Name	Total_Records
HumanResources	Department	16
HumanResources	Employee	290
HumanResources	EmployeeAddress	290
HumanResources	EmployeeDepartmentHistory	296
HumanResources	EmployeePayHistory	316
HumanResources	JobCandidate	13
HumanResources	Shift	3

 

SQL BLOG: First Occurrence Of Character In a String

Scenario:

 We need to find the first occurrence of character ie non numeric data in the string without using the built in function.

Example: String 577a     

First occurrence of character : 4

Approach:

i)                    Here I haven’t used any built in functions. First we need to find the number of records in the table.

ii)                   Now for each record, calculate the length of string. This operation is performed by WHILE loop

iii)                 Now for every single record, I have checked wether the character is numeric or non numeric. In case of non numeric, return the current position as the first occurrence otherwise increment the counter by 1

Full code is below:

/* First occurrence of character in a string using table variable */
/* table variable created*/
declare @table1 table(id integer identity(1,1),name varchar(5))
insert into @table1 values ('911av'),('1sdf'),('aaaa')
/* displaying content of table*/
select * from @table1

/* count the total number of records in the table */
declare @rcount integer
select @rcount = COUNT(name) from @table1
/* declare variable for outer loop */
declare @i_rcount integer
set @i_rcount = 1
/* loop that will run for each record */
declare @srecord varchar(10)
declare @result1 varchar(10)
declare @x integer
while(@i_rcount <=@rcount)
begin/* store the individual records here */
select @srecord = name from @table1 where id = @i_rcount
select @result1 = LEN(name) from @table1
set @x = 1   /* checking each character in the record */
   while(@x <=@result1)
   begin
   if(ISNUMERIC(left(@srecord,@x))) <> 1
        begin
        select 'First Occurence of character' +' '+ 'in string:'+' '+' '+ @srecord +' '+'is'+' '+ cast(@x as varchar(3))
        set @x = @result1+1 /* this will exit from the inner loop */
        end
        else
        set @x = @x+1
   end
   set @i_rcount = @i_rcount+1
end  
  
 Sample Output:

First Occurence of character in string:  911av is 4