SSIS Data types and expressions

Here is the link to integration services data types

http://msdn.microsoft.com/en-us/library/ms141036.aspx

Expressions in SSIS:

To convert today’s date (getdate()) to string (Format: 2008-05-07)

SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 10 )

To convert today’s date (getdate()) to string (format: 20080507)

( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )

To convert today’s date (getdate()) to int (Format: 20080507)

(DT_I4) ( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )

Grant permissions to view stored procedure text

Sometimes in a production environment you may want to give developers read access to the text of the stored procedures but not be able to modify or execute them. In SQL Server 2005, you can grant VIEW DEFINITION permissions. Here are different ways to it

To grant view permissions to the entire schema

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]

To grant permissions to specific stored procedure

GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]

To grant view definition on all the stored procedures in the database

--temporary table

DECLARE @tmpTable TABLE (

PK_ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[name] SYSNAME

)

--declare variables

DECLARE @name SYSNAME,

@RowCount INT,

@RecCount INT,

@strSQL VARCHAR(1000)

INSERT INTO @tmpTable ([name])

SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'

-- counters for while

SET @RecCount = (SELECT count(*) FROM @tmpTable)

SET @RowCount = 1

WHILE (@RowCount < @RecCount + 1)

BEGIN

SELECT @name = [name]

FROM @tmpTable

WHERE PK_ID = @RowCount

SET @strSQL = N'Grant VIEW Definition on ' + rtrim(cast(@name AS VARCHAR(128))) + ' to [UserName]'

--Execute the Sql

EXEC(@strSQL)

--Decrement the counter

SET @RowCount = @RowCount + 1

--reset vars, just in case...

SET @name = null

END

SELECT * FROM @tmpTable

Database Snapshots

Database snapshot is a read-only static database which provides the view of data at a point in time. Snapshots use the process of copy on write operation. If a page in the source database is modified for the first time, the original page is copied from the database to the snapshot. Updates after that don’t affect the contents of the snapshot.

Snapshots are very useful to off load historical reports from the OLTP systems. If mirroring is used for high availability, snapshots can be created on the mirror database.

SQL server management studio does not provide an option to create snapshots. The following T-SQL creates a snapshot named ProductSnap_0100 (0100 is the time stamp of when the database is created i.e. 1am) on the Products database with its sparse file named Products_data.ss.

--Drop if the snapshot exists

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ProductSnap_0100')

DROP DATABASE ProductSnap_0100

--Create a snapshot on CORIS database

CREATE DATABASE ProductSnap_0100

ON (NAME = N'Products_Data', FILENAME = N'E:\Snapshot\Products_data_0100.ss')

AS SNAPSHOT OF Products

Here is an excellent blog about how to switch the reports to the new snapshot.

http://blogs.msdn.com/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx