Category Archives: SSIS

Get list of SQL Server Jobs from multiple instance via SSIS package

Get list of SQL Server Jobs from multiple instance via SSIS package

While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one SSIS package to list SQL Server Job of all instance I used to manage and insert that list in one database so that anytime I have list ready if any one ask me about anything related to job.

If I have list of jobs with along with its schedule, I can query every SQL Server instance to check whether every job of the instance ran or not. If any job is not running, I can send automated email with list of “Missing Jobs”.

I will cover complete sequence in two article. This article will show you how to prepare a list of all SQL Server Job from multiple instance.

We might change schedule of certain jobs, we might add/remove some of the jobs so it is necessary to have perfect updated list of jobs so the SSIS package I am going to run in this article, would be scheduled to run everyday and gather the latest/updated list of jobs from all instance and insert it in one database of one instance.

Let us start creating foundation for this SSIS package.

I used to have one database called “DBAdb” in one of the instance of SQL Server and I used to collect DBA related data in “DBAdb” database only so that none of the production database becomes big due to DBA related activity. Apart from “DBAdb” database, we need two tables inside “DBAdb” database.

1.) SQLServerInstance: this table will have details of all SQL Server instance and this will be the base table for our package as our package read detail of SQL Server Instance from this table and collect the JOB details from that particular instance.

2.) JobScheduleDetails: This table will have detail of each job along with its schedule which are collected from the each instance we have listed in “SQLServerInstance” table.

I will have only one entry in “SQLServerInstance” table as I am preparing this article from my laptop but you can have as many entries as you want. Package will be created dynamically which will iterate for each server we list.

Here is the TSQL to create database and tables we have listed above.

CREATE DATABASE DBAdb
GO

USE [DBAdb]
GO

CREATE TABLE [dbo].[SQLServerInstances](
[Sid] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[ConnectionString] [VARCHAR](1000) NULL,
[IsActive] [bit] NULL,
[GetJobSchedules] [bit] NULL
) ON [PRIMARY]

INSERT INTO SQLServerInstances
--you have to give your servername alongwith its connection string
SELECT 'USER12\SQL2K12DEV','Data Source=USER12\SQL2K12DEV;Initial Catalog=msdb;Provider=SQLNCLI10.1;INTegrated Security=SSPI;Auto Translate=False;',1,1
GO

--Create table in centralize "DBADb" database in one instance
--which will collect information of job from all servers.
USE [DBAdb]
GO

CREATE TABLE [dbo].[JobSchedulesDetails](
[Seq] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[JobId] [uniqueidentifier] NULL,
[JobName] [VARCHAR](500) NULL,
[IsEnabled] [VARCHAR](10) NULL,
[Occurrence] [VARCHAR](200) NULL,
[JobSchedule] [VARCHAR](4000) NULL,
[FrequencyType] [VARCHAR](100) NULL,
[Frequency] [INT] NULL,
[StartTime] [VARCHAR](20) NULL,
[EndTime] [VARCHAR](20) NULL,
[StartDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+' ')+[StartTime],0)),
[EndDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+' ')+[EndTime],0)),
[CategoryId] [INT] NULL
) ON [PRIMARY]
GO

Now we are ready with information we want so let us start creating SSIS package.

1.) Create three variables for the package as given in following screen capture.

1DeclareVariable

2.) Get one “Execute SQL Task” in “Control Flow” of your package.

2ExecuteTaskForServerList

I have used following query in “Execute SQL Task”.

SELECT
ServerName,
ConnectionString
FROM
dbo.SQLServerInstances
WHERE
IsActive = 1
AND
GetJobSchedules =1

3.) Above query will return list of SQL Server Instance where we need to collect job details from so we are going to capture result set in one of the variable we have created. Go to “Result Set” tab in Execute SQL Task as given below and following the screen capture.

3ResultSetinVariable

4.)  Take one “Foreach Loop Container” and join it with previously created “Execute SQL Task”. set properties of Foreach task as given in screen capture.

4ForEachLoopFromVariable

5.) need to set other variables for data in Foreach task. see following screen capture.

5ForEachLoopVariableMapping

6.) Since we need latest and updated detail of JOB in master table, we are going to delete old job detail from the table before we insert new details via “Execute SQL Task”.

6DeleteOldRecordOfJobScheduleForServer

Here is the query I have used in above Execute SQL Task.

DELETE FROM DBAdb.dbo.JobSchedulesDetails
WHERE ServerName  = ?

7.) Now take one “Data Flow” task and connect it with “Execute SQL Task” we created to delete data for old job detail before we capture fresh data.

7DataFlowTask

8.) As soon as you double click on newly created “Data Flow” task, you will be forwarded to “Data Flow” tab and you will have to have three task there. 1.) OLE DB Source 2.) Data Conversion 3.) OLE DB Destination.

8DataFlowInsight

OLEDB Source task here plays very crucial role. Following is the query I have used to read job details.

SELECT
cast(SERVERPROPERTY ('servername')  as nvarchar(200)) as ServerName,
a.job_id as JobId,
a.name as JobName,
a.Category_id as CategoryId,
case when a.Enabled = 1 then 'Enable' else 'Disable' end as JobStatus,
CASE [freq_type]
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPUs become idle'
END [Occurrence]
, (CASE [freq_type]
WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' week(s) on '
+ CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
+ ' of every '
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
WHEN 32 THEN 'Occurs on '
+ CASE [freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END
+ ' '
+ CASE [freq_interval]
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
END
+ ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' month(s)'
END ) +
(CASE [freq_subday_type]
WHEN 1 THEN ' once at '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN ' every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 4 THEN ' every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 8 THEN ' every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
END ) as JobSchedule,

(CASE [freq_subday_type]
WHEN 1 THEN 'Once'
WHEN 2 THEN 'Second(s)'
WHEN 4 THEN 'Minute(s)'
WHEN 8 THEN 'Hour(s)'
END ) as 'Every',

(CASE [freq_subday_type]
WHEN 1 THEN NULL
WHEN 2 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 4 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 8 THEN CAST([freq_subday_interval] AS VARCHAR(3))
END ) as 'Between',

(CASE [freq_subday_type]
WHEN 1 THEN  STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

WHEN 4 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

WHEN 8 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')

END ) as StartTime ,

(CASE [freq_subday_type]
WHEN 1 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN  STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 4 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 8 THEN STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
END ) as EndTime
FROM
[msdb].[dbo].[sysjobs] a
INNER JOIN [msdb].[dbo].[sysjobschedules] b
ON (b.job_id = a.job_id)
INNER JOIN [msdb].[dbo].[sysschedules] c
ON (c.schedule_id = b.schedule_id)
ORDER BY a.name

9.) We may have one or multiple instance where we need to collect this information from so we have to connect our OLE DB Source Task dynamically with different server instance. First use the query I gave above and create regular SQL Server connection. Once you are done, go to property of that connection task to change the name and give it dynamic connection string for different server. We have dynamic connection string coming in Foreach Loop and we have mapped that connection string in “ConnectionString” variable we have created in very first steps above.

9DynamicConnection

10.) Connect one “Data Conversion” task with OLE DB Source and see the property I have set in following screen capture.

10DataConversion

Now, we have read job details from server. Now, this is time to insert that detail in “DBAdb” database so that we can use it for our purpose.

10.) set the property of “OLE DB Destination” after connecting it to “Data Conversion Task”.

11OLEDBDestination

11.) We have to map column from source and destination in “OLE DB Destination” task.

12Mapping

Now you are ready to run. Execute the package and see its effect. If the servers you have inserted in “SQLServerInstance” table is having SQL Server Job, you will find job details in your “[JobSchedulesDetails]” table.

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script backup of replication setup of SQL Server by SSIS and SMO

Script backup of replication setup of SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.

Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of replication along with its article of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of replication every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

ReplicationBackupScriptTask

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Microsoft.SQLServer.Replication .NET Programming Interface

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.


using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

Now here is the code you have to place in your “Main” method of script window.


// TODO: Add your code here

string ServerName;

string UserName;

string Password;

string FolderDate;

string NetworkPath;

ServerName = "ServerName";

UserName = "Login";

Password = "Password";

FolderDate = DateTime.Now.ToShortDateString().Replace('\\','_').Replace('/','_');

NetworkPath = @"D:\"+ ServerName.Replace('\\', ' ').ToString() + @"\Replicaiton\"+FolderDate+"\\";

ServerConnection conn = new ServerConnection();

conn.LoginSecure = false;

conn.Login = UserName;

conn.Password = Password;

conn.ServerInstance = ServerName;

Server srv = new Server(conn);

System.IO.Directory.CreateDirectory(NetworkPath);

ReplicationServer RS = new ReplicationServer(conn);

try

{

foreach (ReplicationDatabase RD in RS.ReplicationDatabases)

{

if (RD.HasPublications)

{

foreach (TransPublication TP in RD.TransPublications)

{

TextWriter tw = new StreamWriter(NetworkPath + "\\" + TP.Name.ToString() + ".sql");

tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));

tw.Close();

}

}

}

}

catch (Exception eh)

{

//MessageBox.Show(eh.ToString());

}

Dts.TaskResult = (int)ScriptResults.Success;

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

Here is the screen capture I have received after I ran it manually.

ScriptBackupReplicationPublisherSSISPackageRun

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Backup Linked Server script in SQL Server by SSIS

Backup Linked Server script in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database only, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for all Linkedserver in SQL Server instance by SSIS.

As long as possible, I, personally, try to avoid linked server but even in some scenario DBA used to use linked server because of any reason (don’t want to start debate of whether to use linked server or not). while making a plan for failover or for creating another server/instance of one of the production server, DBA has to keep Linked Server in mind otherwise some package, view, SP etc. can break in new server/instance if it is uses linked server.

Let us now create one SSIS package to generate the script of all Linked Server from SQL Server. I used to keep monthly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of linked server every month.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
'Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ''A'' and name = ''ALL SETTABLE OPTIONS''

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 open d

fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname

SET NOCOUNT ON

while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values(''--------------------'' + @Server + ''--------------------'')
If @status in (64,65)
Begin
Insert Into @LinkedServer values( ''sp_addserver'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''''')
Insert Into @LinkedServer values('' GO'')
If @status = 64
Begin
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values( '' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values( '' @optname = ''''rpc'''','')
Insert Into @LinkedServer values( '' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
End
exec (''declare r cursor for select l.name, r.remoteusername from sysremotelogins r join sysservers s on r.remoteserverid = s.srvid join syslogins l
on r.sid = l.sid where s.srvname = ''''''+ @server + '''''''') open r fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ''sp_addremotelogin'')
Insert Into @LinkedServer values('' @remoteserver = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @loginame = ''''''+ @loclogin + '''''','')
Insert Into @LinkedServer values('' @remotename = ''''''+ @rmtlogin + '''''''')
Insert Into @LinkedServer values('' GO'')

fetch next from r into @loclogin, @rmtlogin end close r deallocate r
End
Else
Begin
If exists (select * from tempdb..sysobjects where name like ''#tmpsrvoption%'')
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption ( srvoption varchar(30) )

insert #tmpsrvoption
select v.name from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid and (v.number & s.srvstatus)=v.number and (v.number & isnull(@allsetopt,4063)) <> 0 and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = ''A'' Insert Into @LinkedServer values( ''sp_addlinkedserver'')

Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''''')
Insert Into @LinkedServer values('', @srvproduct = '''''' + @srvproduct + '''''''')
If @srvproduct <> ''SQL Server''
Begin
Insert Into @LinkedServer values( '', @provider = '''''' + isnull(@provider,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @datasrc = '''''' + isnull(@datasrc,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @location = '''''' + isnull(@location,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @provstr = '''''' + isnull(@provstr,''NULL'') + '''''''' )
Insert Into @LinkedServer values('', @catalog = '''''' + isnull(@catalog,''NULL'') + '''''''')
End

Insert Into @LinkedServer values( '' GO'')
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''rpc'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
Insert Into @LinkedServer values(''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''rpc out'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
Insert Into @LinkedServer values(''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''data access'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')

declare s cursor for SELECT srvoption from #tmpsrvoption open s
fetch next from s into @srvoption while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''''+ @srvoption + '''''','')
Insert Into @LinkedServer values('' @optvalue = ''''true'''''')
Insert Into @LinkedServer values('' GO'')

fetch next from s into @srvoption
End

close s
deallocate s

If exists (select * from tempdb..sysobjects where name like ''#tmplink%'')
Begin
drop table #tmplink
End

create table #tmplink ( rmtserver sysname, loclogin sysname null, selfstatus smallint, rmtlogin sysname null )

insert #tmplink exec (''sp_helplinkedsrvlogin ''''''+ @server + '''''''')

declare ll cursor for select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin open ll fetch next from ll

into @loclogin, @selfstatus, @rmtlogin while (@@FETCH_STATUS<>-1)

begin If (@selfstatus = 1 and @loclogin is null)

Begin

Insert Into @LinkedServer values( ''sp_addlinkedsrvlogin'')

Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')

Insert Into @LinkedServer values('' @useself = ''''true'''''')

Insert Into @LinkedServer values('' GO'')

End

Else If (@selfstatus = 1 and @loclogin is not null)
Begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''true'''','')
Insert Into @LinkedServer values('' @locallogin = ''''''+ @loclogin + '''''','')
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
Insert Into @LinkedServer values('' GO'')
End Else If (@selfstatus = 0 and @rmtlogin is null)
Begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''false'''','')
Insert Into @LinkedServer values('' @locallogin = NULL,'')
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
Insert Into @LinkedServer values('' GO'')
End Else If (@selfstatus = 0)
Begin
exec (''declare password cursor for select l.password from master..sysservers s join master..syslogins l on s.srvid = l.sid
where s.srvname = ''''''+ @server + '''''' and l.name = ''''''+ @rmtlogin + '''''''')

open password fetch next from password into @rmtpass while @@fetch_status = 0
begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''false'''','')

If (@loclogin is null)
Begin
Insert Into @LinkedServer values('' @locallogin = NULL,'')
End
Else
Begin
Insert Into @LinkedServer values('' @locallogin = ''''''+ @loclogin + '''''','')
End

If (@rmtlogin is null)
Begin
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
End
Else
Begin
Insert Into @LinkedServer values('' @rmtuser = ''''''+ @rmtlogin + '''''','')
End

If (@rmtpass is null)
Begin
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
End
Else
Begin
Insert Into @LinkedServer values('' @rmtpassword = ''''''+ @rmtpass + '''''''')
End

Insert Into @LinkedServer values('' GO'')

fetch next from password into @rmtpass
end
close password

deallocate password
End

fetch next from ll into @loclogin, @selfstatus, @rmtlogin End close ll deallocate ll
End

If @netname <> @server
Begin
Insert Into @LinkedServer values( ''sp_setnetname'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @network_name = ''''''+ @netname + '''''''')
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname
End close d deallocate d Select [LinkedServer] From @LinkedServer'

Exec (@SQL)

Here is the screen capture of “OLE DB Source” property.

2PropertyofDataflow

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “LinkedServer.sql” which I already had. You can keep blank SQL file in your destination.

33FileDestination

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “LinkedServer” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

FinalExecutionBackupLinkedServerScriptSSISPackage

We have many different server/instance and need script for all Linked Server from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

Note: If you have set password for your linked server, this script won’t decrypt password and give it to you. You have to manually change it in the script this SSIS package will create.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

I have written few more Linked Server related articles in past, have a look if you are interested.

  • Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)
  • Linked Server in SQL Server 2005 from ACCESS 2007 (click Here)
  • Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)
  • Linked Server is not configured for data access Error: 7411 in SQL Server 2005(Click Here)
  • Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkServerName” was unable to begin a distributed transaction. (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of mail profile in SQL Server by SSIS

Automatic script backup of mail profile in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and “Automatic script backup of email operator in SQL Server by SSIS and SMO”, today I come up with article which generates the script for all mail profile in SQL Server instance by SSIS.

Mail Profile is one of the important configuration for DBA because of this mail profile I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same mail profile(s) and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package to generate the script of all mail profile of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.


SELECT '--Create Account' AS CMD

UNION ALL

SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ''' + m.name + ''',

@description = ''' + m.description + ''',

@email_address = ''' + m.email_address + ''',

@replyto_address = ''' + m.replyto_address + ''',

@display_name = ''' + m.display_name + ''',

@mailserver_name = ''' + s.servername + ''',

@mailserver_type = ''' + s.servertype + ''',

@port = ''' + cast(s.port as nvarchar) + ''',

@username = ''' + isnull(c.credential_identity,0) + ''',

@password = ''x'',

@use_default_credentials = 0,

@enable_ssl = 0' AS CMD

FROM msdb.dbo.sysmail_account m

LEFT OUTER JOIN msdb.dbo.sysmail_server s

ON m.account_id = s.account_id

LEFT OUTER JOIN master.sys.credentials c

ON s.credential_id = c.credential_id

UNION ALL

SELECT '--Create Profile' AS CMD

UNION ALL

SELECT '

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ''' + name + ''',

@description = ''' + isnull(description,'NULL')+ '''

' AS CMD

FROM msdb.dbo.sysmail_profile

UNION ALL

SELECT '--Create Link For Profile to Account' AS CMD

UNION ALL

SELECT '

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = '''+ a.name +''',

@account_name = ''' + b.name + ''',

@sequence_number = 1 ' as CMD

FROM msdb.dbo.sysmail_profile as a

JOIN msdb.dbo.sysmail_account as b on a.name = b.name

GO

Here is the screen capture of “OLE DB Source” property.

2OLEDBSource

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “MailProfile.sql” which I already had. You can keep blank SQL file in your destination.

3FlatFileProperty

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “CMD” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

4SuccessfullyRanPackage

We have many different server/instance and need script for all mail profiles from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of email operator in SQL Server by SSIS and SMO

Automatic script backup of email operator in SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and today I come up with article which generates the script for all email operator in SQL Server instance by SSIS and SMO.

Email Operator is one of the important configuration for DBA because of this operator I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same operator and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of all email operator of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

1Package

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.


using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using System.IO;

using System.Collections.Specialized;

Now here is the code you have to place in your “Main” method of script window.


// TODO: Add your code here

StringCollection sc = new StringCollection();

ScriptingOptions so = new ScriptingOptions();

so.IncludeDatabaseContext = true;

string ServerName;
string UserName;
string Password;
string FolderDate;

ServerName = "ServerName";
UserName = "Login";
Password = "Password";
FolderDate = DateTime.Now.ToShortDateString().Replace('\\','_').Replace('/','_');

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = UserName;
conn.Password = Password;
conn.ServerInstance = ServerName;

Server srv = new Server(conn);
System.IO.Directory.CreateDirectory("D:\\MyServerName\\Operators\\" + FolderDate.ToString());
try
{
string script = "";
string OperatorName;

//Loop over all the jobs
foreach (Operator O in srv.JobServer.Operators)
{
//Output name in the console
Console.WriteLine(O.Name.ToString());

OperatorName = O.Name.ToString();
sc = O.Script(so);

//Get all the text for the job
foreach (string s in sc)
{
script += s;
}

//Generate the file
TextWriter tw = new StreamWriter("D:\\MyServerName\\Operators\\" + FolderDate.ToString() + "\\" + OperatorName.Replace(':', '_').ToString() + ".sql");
tw.Write(script);
tw.Close();

//Make the string blank again
script ="";
}
}
catch
{
//MessageBox.Show(eh.ToString());
}

Dts.TaskResult = (int)ScriptResults.Success;

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script all SQL Server Job automatically by SSIS and SMO

Script all SQL Server Job automatically by SSIS and SMO

As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.

We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.

Let us see, how it goes!!!

Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.

1ScriptTaskEditor

Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;

Here is the code I have written in “Main()” method of script editor.

// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);

try
{
string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
{
script ="";
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);

//concate the text of job
foreach (string s in strCol)
{
script += s;
}
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(':', '_').ToString() + ".sql");
tw.Write(script);
tw.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}

Here is the screen capture of my script editor.

2ScriptEditor

Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automate Trace file import process with SSIS

Automate Trace file import process with SSIS

I have previously written two articles on this subject “Profiler Trace”.

Start and stop server side profiler trace automatically in SQL Server 2012: This article shows how to create profiler trace file automatically for our business hours.

Read SQL Server Profiler Trace files with SELECT statement: This article shows how to read trace file with TSQL which was generated by the previous article.

Reading trace file manually with TSQL and after reading the trace file, delete those trace file is, somehow, repetitive as well as boring work to do. I used to create one SSIS package which reads profiler trace file and insert data into SQL Server table, delete old trace file and then remove 7 days old trace data from SQL Server table.

Let us create one SSIS project in Visual Studio 2010 and follow the steps given:

1.) Have one “Execute SQL Task” in SSIS Package. Assign following INSERT statement in “SQLStatement” property of Execute SQL Task and create one connection of your database.


Insert into Profilerlog

SELECT

ServerName

,DatabaseName

,SPID AS SessionId

,ObjectName

,EventClass AS EventNumber

,RowCounts

,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds

,CPU AS CPUInMiliseconds

,StartTime

,EndTime

,TextData

FROM fn_trace_gettable('D:\TraceCollection\Adventureworks2012.trc', DEFAULT)

go

You must have “ProfilerLog” table in your database which can take output of the SELECT query we have executed on trace file. I have my trace file at “D:\TraceCollection\Adventureworks2012.trc”, you can have your own path. Have a look at screen capture of the Execute SQL Task.

1ExecuteSQLTask

2.) Once we have imported data from trace file, we can delete all trace file which we have already read so let us have “Foreach Loop Editor” and iterate it for each trace file we have in our folder so that we can remove it. Set the property of Foreach loop as displayed in screen capture.

2foreach

3.) You have to create variable “FileName” for package and it should be set to 0 as an Index in Foreach Loop’s “Variable Mappings” tab. You can refer above screen shot where you can find “Variable Mappings” right below “Collections” in left hand side.

4.) put “File System Task” inside foreach loop container and set “Delete file” in Operation property as given in following screen shot.

3FileSystemTask

5.) now have one more execute SQL task under the foreach loop and give following query which will remove 7 days old data from ProfilerLog table.


delete from Profilerlog where convert(varchar(10),StartTime,101) &lt; convert(varchar(10),getdate()-7,101)

go

6.) finally your package will looks like this:

4PackageLook

Now you are ready to schedule this package in your SQL Server Agent job which runs once (may be at midnight) and import trace file.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Sometime back I have already written about world famous stored procedure “SP_WhoIsActive” created by SQL Server GURU Mr. Adam Machanic (Blog). I used to have this SP in all of my important production server to keep an eye on the query performance. This SP returns huge amount of data based on the workload on the SQL Server so I always wish to maintain separate database in separate server for DBA related activity to keep production database free from DBA related activity as much as possible.

Note:

I have provided link of new version of “SP_WhoIsActive” in the first paragraph of this article but I am still using little older SP as I have already set it up in many of my servers.

I am not able to find that old SP from the blog of Mr. Adam Machanic and hence I am providing it here for the reference of my blog reader. Please download it from here as I have demonstrated this article based on the SP I am using.

I have created one SSIS package which is hosted on my DBA server and executes “SP_WhoIsActive” in each instance of production server in every few seconds or minutes, collects the data from “ SP_WhoIsActive” and stores those data in DBA database. My DBA database is named “DBADB”.

I assume that you already have “SP_WhoIsActive” in one of your production database in one SQL Server Instance and creating package for that. I also assume that you have “DBADB” database which collects and stores data.

Let us create package then:

Create one SSIS project and take “Execute SQL Task” in package.

Have some variables declaration for your package.

1VariableInSSISPackage

Se the connection of “Execute SQL Task” with database of your production server where you have created “SP_WhoIsActive” stored procedure. Once connection is made, have the following TSQL in “Execute SQL Task”.

IF OBJECT_ID('tempdb..#ActiveProcesses') IS NOT NULL
DROP TABLE #ActiveProcesses

CREATE TABLE #ActiveProcesses(
[dd hh:mm:ss.mss] [varchar](15) NULL,
[dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
[session_id] [smallint] NOT NULL,
[sql_text]  NULL,
[sql_command]  NULL,
[login_name] [nvarchar](128) NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[CPU] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[blocking_session_id] [smallint] NULL,
[blocked_session_count] [varchar](30) NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[query_plan]  NULL,
[locks]  NULL,
[used_memory] [varchar](30) NULL,
[status] [varchar](30) NOT NULL,
[open_tran_count] [varchar](30) NULL,
[percent_complete] [varchar](30) NULL,
[host_name] [nvarchar](128) NULL,
[database_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[additional_info]  NULL,
[start_time] [datetime] NOT NULL,
[request_id] [int] NULL,
[collection_time] [datetime] NOT NULL,
[seq] [int] IDENTITY(1,1) NOT NULL
)

EXEC sp_WhoIsActive @destination_table='#ActiveProcesses'

SELECT
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
CONVERT(nvarchar(max),[sql_text]) AS [sql_text],
CONVERT(nvarchar(max),[sql_command]) AS [sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
CONVERT(nvarchar(max),[query_plan]) AS [query_plan],
CONVERT(nvarchar(max),[locks]) AS [locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
CONVERT(nvarchar(max),[additional_info]) AS [additional_info],
[start_time],
[request_id],
[collection_time],
[seq]
FROM #ActiveProcesses

Here you can find the screen capture of

2ExecuteSQLTaskGeneralProperty

Go to result set property of “Execute SQL Task” and take the output of SELECT query into one dataset variable we have created.

3Dataset

Now, take one Foreach loop task and iterate it for “ADO” with the dataset we have created.

4ForEachADOIterate

Now we have to map the variable to get the value from dataset in Foreach Loop task.

5VariableMapping

Have one more “Execute SQL Task”, inside the “foreach loop taks”, which insert row from dataset to “DBADB” database. Here is the TSQL which we used to use in “Execute SQL Task”.

insert into dba.ActiveProcesses
(
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
[sql_text],
[sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
[query_plan],
[locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
[additional_info],
[start_time],
[request_id],
[collection_time]
)
select ?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?

6ExecuteSQLforDBADB

Once you set SQLStatement in “Execute SQL Task”, go to “Parameter Mapping” and set variables there.

7ParameterMappingForDBADB

Now, you are absolutely ready to schedule your SSIS package in JOB or in Schedule task which get executed at every few seconds and collect the data for you in DBADB database which you can analyze at your convenience.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Looping through dataset in SSIS

Looping through dataset in SSIS

I have written quite a few SSIS article so far but two days back I have received an email from one of the user where he wants to iterate through dataset like we used to loop on each records of dataset in .NET. I thought to provide him solution in form of blog article so that everyone can get benefit of it.

I am going to use my AdventureWorks2012 database in SQL Server 2012 along with Visual Studio 2010 to demonstrate this article.

Let us assume that we have two tables called “Sales.SalesOrderHeader” and “Sales.SalesOrderDetails” in our “AdventureWorks2012” database. We have one OrderID 43659” and we want to change (update) orderQty filed. If SalesOrderDetailID is even, we want to change OrderQty to 2 otherwise we want to change it to 1. We have direct TSQL query to do this task for us but we will use ForEach loop container to do this task to demonstrate how dataset works in SSIS.

Create SELECT query in New Query Window of SQL Server Management Studio in Adventureworks2012 database.

SELECT
SOH.SalesOrderID
,SOD.SalesOrderDetailID
,SOH.OrderDate
,SOH.DueDate
,SOD.ProductID
,SOD.OrderQty
FROM
sales.salesorderheader AS SOH JOIN sales.salesorderDetail AS SOD
ON SOD.SalesOrderID=SOH.SalesOrderID
WHERE SOH.SalesOrderID=43659

Create one SSIS project from Visual Studio 2010 and follow the steps given below:

1.) Create variables to contain resultset going to be return from above select query and also create variable to hold value of each field inside the foreach loop. Here is the screen capture of variable window. (you can open variable window by right click on “Control Flow” tab, you will find “Variables” option in popup menu)

1Varibles

2.) Create one Execute SQL Task and create one OLEDB connection inside that for Adventureworks2012 database also have the SELECT query copy from SSMS which we have created above and paste it there. Follow the screen capture.

2ExecuteSQLTaskForSELECT

3.) Take ForEach Loop Container and set the following Property.

3ForEachLoop

4.) now move to “Variable Mappings” tab in same Foreach Loop Editor to set parameter mapping.

4ParameterMappingInForEach 

5.) now set Script task to check whether SalesOrderDetailID is even or odd.

5ScriptTask

Here is the script which you have to write down in “Main” after clicking on “Edit Script”.

// TODO: Add your code here
if ((int)Dts.Variables[&quot;SalesOrderDetailID&quot;].Value % 2 == 0)
{
Dts.Variables[&quot;isOdd&quot;].Value = true;
}
else
{
Dts.Variables[&quot;isOdd&quot;].Value = false;
}

Dts.TaskResult = (int)ScriptResults.Success;

6.) Now create two SQL Task and connect it with Script task created above. One connector (“Precedence Constraint”) will have “true” value and another will have “false”. I have taken screen capture of first connector with true value.

6ConnectorProperty

7.) Now both the execute SQL task for update will have UPDATE query. First UPDATE will have OrdQty value 2 as static and second will have 1. I am taking screen shot of first SQL Task inside foreach loop.

7UpdateQuery

8.) once we set all of the above value from “General” tab, we will click on “Parameter Mapping” to provide value of SalesOrderID and SalesOrderDetailID to UPDATE query. Here is the screen capture.

8UpdateQuery

9.) Now when you finally execute package, it will looks like this (if it executed successfully):

9FinalExecution

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Fuzzy lookup in SSIS 2008 to keep data integrity

“Human makes mistakes” which is quite obvious. While making a data entry it is possible to make typo but as a database professional, it is our duty to keep data consistent.  Fuzzy Lookup is helpful in this case. Before we start making package in SSIS, let us have some pre-preparation for that. We are going to create one source table (it could be any source like excel, csv file etc. but we are making it in SLQ Server), one reference table which is guaranteed to have proper data. Here is the TSQL to create, source and reference, table and insert some dummy data.
create table fuzzyLookupSource
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupSource
select ‘Rites’,‘Shah’,’02/07/1980′ union all
select ‘Rajen’,‘Shah’,’03/31/1983′ union all
select ‘Dharmesh’,‘Kalaria’,’04/09/1980′  union all
select ‘Jesica’,‘Cruize’,’05/05/1980′  union all
select ‘Roger’,‘Moore’,’04/15/1980′
GO
create table fuzzyLookupReference
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupReference
select ‘Ritesh’,‘Shah’,’02/07/1980′ union all
select ‘Rajan’,‘Shah’,’03/31/1983′ union all
select ‘Jessica’,‘Cruise’,’06/05/1980′  union all
select ‘Dharmesh’,‘Kalaria’,’04/09/1980′
GO
Observe the data in both table, in first, source, table, there are some typos which you can compare with your second, reference, tables and get the purified data.
Anyway, once you are ready with both the tables , create one new project in BIDS (Business Intelligence Development Studio) and drag one “DataFlow” task from tool box to your “Control Flow” tab. Double click on “DataFlow” task to configure it so that it would redirect you to “DataFlow” tab.
Now, create one “Ado Net Source” which will refer our “fuzzyLookupSource” table in sql server database. Double click on “Ado Net Source” to configure it and look at below image to have crystal clear idea about its configuration.

Now,  drag “fuzzy lookup” transformation task below your “Ado Net Source” and connect extended green arrow from “Ado Net Source” to your fuzzy lookup. Double click on “Fuzzy Lookup” task to configure it.

In “Reference Table” tab, give reference of your database and our reference table which is “FuzzyLookupReference” in our case. Look at image below for more idea.

Click on “Columns” tab to configure which column to check with reference from source table and select “firstName” and “lastName” column and connect it so that our fuzzy lookup task will compare these two fields from source to reference table.

Once you configure “columns”, you have to click on “Advanced” tab, you can set “Similarity Threshold” which will give you how much identical both fields are…. If it is 1 than it is perfect match, if it is 0 than no match or data not present in reference table so more near to 1, good match it is. We are not going to take any decision like if it is greater than .50 then do this otherwise do that so it would be ok if you don’t change “Similarity Threshold”.

Now, drag “SQL Server Destination” task so that this matched and unmatched data could fall in SQL Server table, though we have not created any SQL Server table for this so far. Connect green extended arrow from “Fuzzy Lookup” transformation task to “SQL Server Destination” task. Before we configure “SQL Server Destination” we would like to do one more thing. Double click on GREEN arrow between Fuzzy Lookup task and SQL Server destination task.

We would like to see data in grid while running this package and before it fall into our destination table, we are going to specify this only now.
As soon as you click on green arrow, it will open “Data Flow Path Editor”, click on “Data Viewer” tab and click on “Add” button to add “Grid”.

Now, double click on “SQL Server Destination” task to configure it. Give details of your SQL Server and database into “Connection Manager” name. since we don’t have destination table already created for our data, we are going to click on “New” button besides “Use a table or view” property which will create one destination table in our SQL Server.

Now you are ready to run your package, hit F5 to run it, when it crosses Fuzzy Lookup Task, it will show you data in grid, check it and click on “Green Arrow” above the grid in same dialog box so that data falls into our SQL Server table.

you can later on check the same data into SQL Server by executing TSQL Query, while generating new table in SQL Server, if you didn’t have rename the table, it would be by default [SQL Server Destination]. So you can execute something like

SELECT * FROM [SQL Server Destination]
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah