Wednesday, April 20, 2011

Check Column Existence Before Add/Drop in the Table

IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C ON O.OBJECT_ID = C.OBJECT_ID WHERE o.NAME ='TableName' AND C.NAME ='ColumnName')
BEGIN
Alter Table dbo.TableName Add ColumnName DataType
END


IF EXISTS(SELECT 1 FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C ON O.OBJECT_ID = C.OBJECT_ID WHERE O.NAME ='TableName' AND C.NAME ='ColumnName')
BEGIN
Alter Table dbo.TableName Drop Column 'ColumnName'
END

Wednesday, April 13, 2011

How to create Dynamic Txt Flat file as destination in SSIS

1. Create a new flat file connection through Connection Manager. 2. Once created then select the properties... 3. Click on ... option in Expressions 4. Select Property as ConnectionString 5. Write done the Expression @[User::dest_path] + "FileName" + RIGHT("0" + (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate())),2)+RIGHT("0" + (DT_STR,4,1252)DAY( DATEADD( "dd", 0, getdate() )),2)+ (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+ ".txt" NOTE: @[User::dest_path] is a variable where we have mentioned the location (URL) so it will append this file in the same path.

Tuesday, April 12, 2011

Alternate of ISNULL command

SET CONCAT_NULL_YIELDS_NULL ON; GO select EMPID, 'F'+FirstName , 'L'+LastName from EMP GO SET CONCAT_NULL_YIELDS_NULL OFF; select EMPID, 'F'+FirstName , 'L'+LastName from EMP