Different ways to execute a SQL Server SSIS package

Refer the following link to see the different ways to Execute a SQL server SSIS Package

http://www.mssqltips.com/tip.asp?tip=1775

Dynamically generate connestion String Using Script Task IN SSIS

Dynamically generate connestion String Using Script Task
(visual basic)

Public Sub Main()

Dts.Variables("Destn").Value = "Data Source=" & Dts.Variables("ServerName").Value.ToString & ";User ID=" & Dts.Variables("DBUserName").Value.ToString & ";Password =" & Dts.Variables("DBPwd").Value.ToString & ";initial Catalog=" & Dts.Variables("DBName").Value.ToString & ";Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Auto Translate=False;"

Dts.TaskResult = ScriptResults.Success

End Sub


Where Destn, ServerName, DBUserName, DBPwd, DBName are SSIS Variables

SQL OPTIMIZATION

OPTIMIZATION

In Subquery

Some of us will use a subquery within the IN operator such as this.

SELECT * FROM TABLENAME 

WHERE COLUMN IN

(SELECT COLUMN FROM TABLENAME)

Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable

WHERE dummytable.COLUMN = TABLE.COLUMN;

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable

WHERE dummytable.COLUMN = TABLE.COLUMN;\

     Using dummy table is better than using an IN operator to do a subquery. Alternative, an exist operator is also better.

SSIS Connection String Format

Connection String is assigned to a variable in the below format
Data Source=10.200.2.55;User ID=sa;Password =Welcome123;initial Catalog=MedallMart;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Auto Translate=False;

Best way to write a dynamic querry

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT [name], [address] ' + 'FROM [USERS] Where [username]= ‘

SELECT @sql = @sql + ' [username] LIKE @username'

EXEC sp_executesql @sql, N'@username varchar(200)', @username

SQL Designer

http://ondras.zarovi.cz/sql/demo/

The sql designer is a webbased application to design a database.

INSERT TO TEMP TABLE FROM A DYNAMIC QUERRY

INSERT TO TEMP TABLE FROM A DYNAMIC QUERRY

declare @query varchar(200)

set @query='SELECT      1 as NO,

                                      ''Madhan'' as Name '

insert into #temp (No,Name)

exec (@query)

SELF JOIN

Reference:http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/




USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

We will now use inner join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

From the result set, we can see that all the employees who have a manager are visible. However we are unable to find out the top manager of the company as he is not visible in our resultset. The reason for the same is that due to inner join, his name is filtered out. Inner join does not bring any result which does not have manager id. Let us convert Inner Join to Outer Join and then see the resultset.

-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') ASManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Once we convert Inner Join to Outer Join, we can see the Top Manager as well. Here we have seen how Self Join can behave as an inner join as well as an outer join.

As I said earlier, many of you know these details, but there are many who are still confused about this concept. I hope that this concept is clear from this post.

JOIN Tutorial

Thanks for sql blog authority
reference http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

CREATE TABLE table1

(ID INT, Value VARCHAR(10))

INSERT INTO Table1 (ID, Value)

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 4,'Fourth'

UNION ALL

SELECT 5,'Fifth'

GO

CREATE TABLE table2

(ID INT, Value VARCHAR(10))

INSERT INTO Table2 (ID, Value)

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 6,'Sixth'

UNION ALL

SELECT 7,'Seventh'

UNION ALL

SELECT 8,'Eighth'

GO

SELECT *

FROM Table1

SELECT *

FROM Table2

GO

/* INNER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

INNER JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* LEFT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* RIGHT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* OUTER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* LEFT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t2.ID IS NULL

GO

/* RIGHT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL

GO

/* OUTER JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL OR t2.ID IS NULL

GO

/* CROSS JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

CROSS JOIN Table2 t2

GO

DROP TABLE table1

DROP TABLE table2

GO

Funtion to Seperate Numbers From Charaters

Create Function [dbo].[NoLetters] (@number varchar(20))

Returns varchar(20) 

as 

begin 

 declare @c int 

 set @c=65 

 while @c<65+26 

 begin 

 set @number=replace(@number,char(@c),'') 

 set @c=@c+1 

 end 

return(@number) 

end 

 

----------------------------- 

Input 'MA1234'

Output '1234'

-----------------------------  

Adding Trailing Spaces to a Column

Declare @MaxLengthInColumn

SELECT @MaxLengthInColumn=MAX(len(ColumName) ) from TableName

SELECT ColumnName+space(   @MaxLengthInColumn -  len(ColumName)   ) FROM TableName

 Temp Table Vs  Table Variable 

**      Temp Table has the scope only within the transaction

**      Table Varible has the scope within that current session

BEGIN TRAN

declare @var table (id int, data varchar(20) )

create table #temp (id int, data varchar(20) )

insert into @var

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

insert into #temp

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

select * from #temp

select * from @var

ROLLBACK

select * from @var

if object_id('tempdb..#temp') is null

    select '#temp does not exist outside the transaction'

SQL SELECT To HTML MAIL

--SQL SELECT  To HTML MAIL DECLARE @to varchar(max)
DECLARE @sub varchar(max)
DECLARE @body nvarchar(max)       
DECLARE @return varchar(2)=char(13)
DECLARE @cc varchar(max)
Declare @resolution varchar(max)
Declare @vtableHTML as varchar(max)
SET @vtableHTML =          
N'<H1> Medall HR-Task '+ CONVERT(VARCHAR(9), GETDATE(), 6)   + ' </H1>'+          
N'<table border="1">' +          
N'<tr width=100pixcel><th>TASK NAME</th>' +
N'<th>TASK DESCRIPTION</th>' +            
N'<th>PROJECT</th>' +          
N'<th>PRIORITY</th>' +          
N'<th>ASSIGNED BY</th>' +          
N'<th>START DATE</th><th>END DATE</th>' +          
N'<th>STATUS      </th>' +          
N'<th>REPETITIVE</th>'+  
N'<th>RESOLUTION</th>'+           
CAST (( SELECT td = T.TASK_NAME, '',          
 td = TASK_DESC, '',          
 td = project_name, '',          
 td = priority_desc, '',          
 td = EMPLOYEE_NAME, '',          
 td = CONVERT(VARCHAR(11),START_DATE,106), '',          
 td = CONVERT(VARCHAR(11),DUE_DATE,106), '',          
 td = status, '',          
 td = case when repetitive=1 then 'YES' else 'NO' end  , '',          
 td = resolution , ''
--select *
FROM TASKS
WHERE TASK_ID=2
      FOR XML PATH('tr'), TYPE  ) AS NVARCHAR(MAX) ) + N'</table>' ;   
--SELECT @vtableHTML
  EXEC MSDB.DBO.SP_SEND_DBMAIL   

  --@PROFILE_NAME = 'TEST', 

@RECIPIENTS=@TO, 
@SUBJECT = 'TASK' ,   
@copy_recipients =@CC,
@BODY = @vtableHTML, 
@body_format     ='HTML'