Archive

Archive for the ‘SQL 2005’ Category

SSIS – Daily Refresh Job

In this post I am going to explain the steps we should follow to create an SSIS package that run’s every night. The package will ensure already imported records are updated and new records are created in the destination system.

For demonstration I am assuming a source system, say CRM exists and it has a view as shown below

s0

The job we are going to create will import this data into destination system, say Employee table as shown below

s01

Now open SQL Business Intelligence Studio. Create new project and select “Integration Services Project” as shown below

s1

From toolbox, drop a “Data Flow Task”

s2

Double click to go inside the Data Flow Task and add a new OLE DB source control

s3

Double click OLE DB Source control.

s4

First we need to setup a new connection. For that click on the New button, enter the source database information. Once completed, select the connection. And set other properties as shown below

s5

Next step is to add a Lookup transformation. This will help us to identify whether the record exists in the destination or not.

s8

Before that we should add a new connection to the destination.

s6

Now we can setup the lookup transformation as shown below

s7

Lookup transformation by default fail if no match is found. In our case for new records this scenario will come up. To ensure new records pass through the pipeline we should set the Error Output properly.

s91

Next, add a conditional split control

s9

Drop the data flow arrow into conditional split control, which brings up following dialog. Select “Lookup Match Output”

s90

Double click Conditional Split control and set following values

s92

Now we should add the ole db destination and a ole db command controls. To “OLE DB Destination” we should select the input “New_Records” from Conditional Split. To “OLE DB Command” we should select the input “Modified_Records” from conditional split.

Make following changes to OLE DB Destination

s93

s94

Make following changes to OLE DB Command control

s95

s96

s97

s98

Now the package is ready for testing, hit F5 to run the package

s99

In my next post I will show how to schedule this package to run every night.

Thanks!

Categories: Misc, SQL 2005, SQL 2008 Tags: ,

SQL Search For String In Database

September 18, 2012 Leave a comment

I use following query to generate a set of “SELECT” statement which can later be executed to search for a given string in the database

select 
'select distinct ''' + tab.name + '.' + col.name + '''  from 
[' + tab.name + '] where [' + col.name + '] like ''%TEXT_TO_SEARCH%'' union ' 
from sys.tables tab 
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id) 
where tab.type_desc ='USER_TABLE' 
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');

Replace TEXT_TO_SEARCH with the term you are looking for. You can further enhance this query to search for any type other than string.

Categories: SQL, SQL 2005, SQL 2008

SQL Comma Separated List

Sometimes we might want to create comma separated list of values from a SQL table column. This can be easily achieved using below query

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+''',''' ,'') + ColumnName
FROM TableName
SELECT @listStr
Categories: SQL, SQL 2005, SQL 2008

SQL List All Triggers In Database

Query which returns all Triggers in a database

USE YourDBName
GO
SELECT trigger_name = sysobjects.name, 
      trigger_owner = 'x',
      table_schema = s.name,
      table_name = OBJECT_NAME(parent_obj),
      isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), 
      isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
      isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), 
      isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
      isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
      [disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') 
   FROM sysobjects
   INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
   INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
   WHERE sysobjects.type = 'TR'
Categories: SQL, SQL 2005, SQL 2008

SQL List All Tables In Database

Query which returns all tables in a particular database

USE YourDBName
GO
SELECT *
FROM sys.Tables
Categories: SQL, SQL 2005, SQL 2008

SQL List All Stored Procedures

Following query uses Information schema

SELECT * FROM 
DatabaseName.information_schema.routines 
WHERE routine_type = 'PROCEDURE'
Categories: SQL, SQL 2005, SQL 2008

SQL Proper Casing

On one of my recent project I got a requirement to proper case a SQL table column values. The data got into this table is through an SSIS package from a 3rd party database. To tackle this I created a user defined function

CREATE FUNCTION ProperCase(@inpStr nvarchar(8000))
RETURNS nvarchar(8000) AS
BEGIN
     DECLARE @Result nvarchar(8000)
     SET @inpStr = LOWER(@inpStr) + ' '
     SET @Result = ''
     WHILE 1=1
     BEGIN
        IF PATINDEX('% %',@inpStr) = 0 BREAK
        SET @Result = @Result + UPPER(Left(@inpStr,1))+
        SubString  (@inpStr,2,CharIndex(' ',@inpStr)-1)
        SET @inpStr = SubString(@inpStr, CharIndex(' ',@inpStr)+1,Len(@inpStr))
     END
     SET @Result = Left(@Result,Len(@Result))
     RETURN @Result
END
Categories: SQL, SQL 2005, SQL 2008