Parameter Mapping in SSIS

Below is the sample package of using parameters in SSIS:

1. Create a package as shown below:

2. Create variables as shown below:

3. Configure Execute SQL Task as shown below, in SQL query replace parameter with '?' symbol.

4. Go to Parameter Mapping and configure as given below:

When you going to pass the parameter value in to SQL command then you need to chose 'Input' as Direction. In this package I want to get Max(SalesID) as an output so I have selected 'Output' as Direction. Simillarly if you are using more than 1 parameters than you need to number the parmeter from '0, 1, 2....'.

5. Now go to th eproperties of "SelectData" variable and configure expression field as shown below:

6. Now you can use the variable "SelectData" in any of your Data Flowsources to retrive the data from table. 

SQL Query to drop all constraints in a table

DECLARE @sql nvarchar(255), @DB nvarchar(50), @Table nvarchar(50)SET @DB = 'source_1'SET @Table = 'Product'WHILE
EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @Table)BEGIN


SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @DB AND table_name = @tableEXEC sp_executesql @sqlEND

SQl query to database backup

The below query helps to take a backup of a DB:


DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name IN ('SalesDW')

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END CLOSE db_cursor

DEALLOCATE db_cursor

SQL 2008 Merge Operation

MERGE is a new command introduced in SQL 2008. This is generally used to compare two tables and insert, update and delete rows in a single statement instead of writing separate statement for each process.

MERGE SYNTAX
MERGE FactSales AS TARGET USING Stg_Sales AS SOURCE ON (FactSales.OrderID = Stg_Sales.OrderID)
WHEN MATCHED AND TARGET.SalesAmount <> SOURCE.SalesAmount OR TARGET.ProductID <> SOURCE.ProductID
THEN UPDATE SET TARGET.ProductID = SOURCE.ProductID, Target.SalesAmount = SOURCE.SalesAmount
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ProductID, SalesNo, SalesAmount, SalesOn)
VALUES (SOURCE.ProductID, SOURCE.SalesNo, SOURCE.SalesAmount, SOURCE.SalesOn)
WHEN NOT MATCHED BY SOURCE
DELETE

This Merge statement can be well used in SSIS SCD Type 1 and 2 also in Incremental load.

How to use SSIS Variable in SQL Command

How to use SSIS Variable in SQL Command

Step 1: Create 2 Variables (String), @User::Column, @User::SQL

Step 2: Configure a column name to @User::Column as (ColumnName)

Step 3:  In OLEDB source set the data access mode to "SQL command from Variable",

Step 4: Configure @User::SQL as

           "SELECT "+@[User::Column]+" AS Name From TableName"

DTexec Command to execute SSIS Package

Use the below command to execute an SSIS package in the path C:\FolderA\FolderB\Folderc\Package.dtsx

dtexec /f C:\FolderA\FolderB\Folderc\Package.dtsx
Here /f stands for file package

Error handling in Stored Procedure

CREATE PROCEDURE myProc @id int
 AS
 BEGIN TRY
  BEGIN TRANSACTION
   DELETE employee WHERE ID = @ID
   COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
   DECLARE @Err AS int
   DECLARE @Msg AS varchar(max)
   SET @Err = @@Error
   SET @Msg = Error_Message()
   ROLLBACK TRANSACTION
   INSERT ErrorTable
   VALUES (@err, @msg)
 END CATCH