Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part6 - SQL Server DBA Tutorial

In this final video of upgrading SQL Server 2008R2 to SQL Server 2012, you will learn following

1- Replication Jobs are tied to the script if you scripted the replication package
2- Script out specific jobs in SQL Server 2008R2
3- How to create database backup maintenance plan from scratch
4- How to configure database backup maintenance plan in SQL Server 2008R2



Upgrade of SQL Server 2008 R2 to SQL Server 2012- SQL Server DBA Tutorial

Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part5 - SQL Server DBA Tutorial

In this video you will learn,
1- How to execute Login script on destination server as part of Login transfer from SQL Server 2008R2 to SQL Server 2012
2- What is Orphan Users in SQL Server
3- Limitation of Login Transfer script
4- How to manually give sys admin permissions to a SQL Server Login
5- How to create Linked server using T-SQL Script
6- How to reset Linked Server Security Password
7- How to create SQL Server Login Trigger using T-SQL Script
8- How to modify replication script to point to valid server
9- How to put password in script during replication setup
10- How to create distribution database using T-SQL Script
11- Troubleshooting Replication in SQL Server

Link to Part 6



Upgrade of SQL Server 2008 R2 to SQL Server 2012 - SQL Server DBA Tutorial

Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part4 - SQL Server DBA Tutorial

As a part of SQL Server 2008 R2 upgrade/migration to SQL Server 2012 suite, this video covers the following topics,
1- How to move Migration folder from one server to another server using UNC path
2- Sequence required to executing the destination checklist
3- How to restore databases on SQL Server 2012
4- How to change databases compatibility from SQL Server 2008R2 to SQL Server 2012

Link to Part 5

Upgrade of SQL Server 2008 R2 to SQL Server 2012 - SQL Server DBA Tutorial

Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part3 - SQL Server DBA Tutorial

In this video you will learn following
1- Mirroring setup system views information
2- Mirroring setup screenshot
3- How to script out SQL Server Agent Jobs
4- How to disable all SQL Server Agent Jobs after scripting out
5- How to veiw DBMail settings in SQL Server
6- How to view Proxy settings in SQL Server
7- How to script out Operators setup in SQL Server
8- How to script out SQL Server 2008R2 Configuration

Link for Part 4




Upgrade of SQL Server 2008 R2 to SQL Server 2012- SQL Server DBA Tutorial

Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part2 - SQL Server DBA Tutorial

In this video you will learn following as part of Upgrade of SQL Server 2008R2 to SQL Server 2012,
1- How to take backup of user databases using backup maintenance plan
2- How to change location of databases backups storage
3- How to execute maintenance plan to take full backup of user databases
4- How to script out Logins on SQL Server 2008R2
5- How to transfer logins from SQL Server 2008R2 to SQL Server 2012
6- How to save T-SQL output in .sql format
7- How to script server role if it exist
8- How to script out Credentials in SQL Server 2008R2
9- How to script out Server Audit and Server Audit Specifications
10- How to script backup devices in SQL Server 2008R2
11- How to script out endpoints if they exist
12- How to look at Mirroring information using system views
13- How to script out Linked server in SQL Server 2008R2
14- How to script out Server level Triggers in SQL Server 2008R2
15- How to Script out Replication in SQL Server 2008R2
16- How to View DBMail Configuration in SQL Server 2008R2

Click here for Part 3



Upgrade of SQL Server 2008 R2 to SQL Server 2012 - SQL Server DBA Tutorial

Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part1 - SQL Server DBA Tutorial

This is first video of SQL Server 2008 R2 upgrade/migration to SQL Server 2012 suite. Video covers following topics for you to learn.
1- Migration and Upgrade strategy
2- Moving databases to destination strategy
3- Upgrade SQL Server 2008 R2 to SQL Server 2012 checklist
4- Checklist covers almost all items that are important in Migration
5- Best practices of Migration from One SQL Server Version to another SQL Server version(higher)
6- How to identify databases that can be migrated to SQL Server 2012 from SQL Server 2008R2

Link for Part 2


Upgrade of SQL Server 2008 R2 to SQL Server 2012 Part1

In Place Upgrade of SQL Server 2012 to SQL Server 2014 Part2 - SQL Server DBA Tutorial

In this video you will learn In-Place upgrade of SQL Server 2012 to SQL Server 2014, you will learn following in this demo,
1- How to install SQL Server 2014 upgrade advisor
2- How to install pre-requisites of SQL Server 2014 upgrade advisor
3- How to take care of Upgrade advisor error " Upgrade advisor missing pre-requisites ....."
4- Best practices of In-place upgrade of SQL Server 2012
5- Rollback options in In-Place upgrade
6- How to run Upgrade Wizard of SQL Server 2014
7- How to view whether SQL Server 2012 is upgrade to SQL Server 2014 after Upgrade Wizard completes
8 - How to upgrade all management components from SQL Server 2012 to SQL Server 2014

For Part One Click Here.


In Place Upgrade of SQL Server 2012 to SQL Server 2014

In Place Upgrade of SQL Server 2012 to SQL Server 2014 Part1- SQL Server DBA Tutorial

In this video you will learn In-Place upgrade of SQL Server 2012 to SQL Server 2014, you will learn following in this demo,
1- How to install SQL Server 2014 upgrade advisor
2- How to install pre-requisites of SQL Server 2014 upgrade advisor
3- How to take care of Upgrade advisor error " Upgrade advisor missing pre-requisites ....."
4- Best practices of In-place upgrade of SQL Server 2012
5- Rollback options in In-Place upgrade
6- How to run Upgrade Wizard of SQL Server 2014
7- How to view whether SQL Server 2012 is upgrade to SQL Server 2014 after Upgrade Wizard completes
8 - How to upgrade all management components from SQL Server 2012 to SQL Server 2014

Link to the Part 2

In Place Upgrade of SQL Server 2012 to SQL Server 2014

How to Call a Stored Procedure in OLE DB Command Transformation with Input Parameters in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

In this video we will be learning how to use a Stored Procedure with input parameters in OLE DB Command Transformation in SSIS Package.

We will be learning below Items in this video

  1. How to Create an SSIS Package 
  2. How to Read Flat File Data by using Data Flow Task
  3. How to Create a Stored Procedure with Input Parameters
  4. How to call a Stored Procedure in OLE DB Command Transformation in Data Flow Task
  5. How to Map the Input Columns to Stored Procedure Parameters in OLE DB Command Transformation
  6. What are the Advantages of using Stored Procedure instead of using a Query with Parameter in OLE DB Command Transformation


Script used in this video to Create Stored Procedure with input parameters for OLE DB Command Transformation.

USE [Test]
GO

CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NULL,
    [RegionCode] [varchar](100) NULL
)

Select * From [dbo].[Customer]

Create procedure dbo.DeleteCustomer
@CustomerName VARCHAR(100),
@RegionCD VARCHAR(100)
AS
BEGIN
Delete from [dbo].[Customer]
where customerName=@CustomerName
and RegionCode=@RegionCD
END

EXEC dbo.DeleteCustomer ?,?



How to Call a Stored Procedure in OLE DB Command Transformation with Input Parameters in SSIS Package



  Related Posts / Videos on OLE DB Command Transformation 

Delete Rows from a Table by using OLE DB Command Transformation in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

In this video we will learn How to perform Delete Operation in OLE DB Command Transformation in SSIS Package.
We will learn the disadvantages of using OLE DB Command Transformation for Delete Operation and what other options we have to perform Delete in SSIS Package.

In this OLE DB Command Transformation Demo , we will learn

  1. How to Read data from Flat File Source in SSIS Package
  2. How to Create Table in SQL Server Database
  3. How to write Delete statement with Input Parameter for OLE DB Command Transformation
  4. How to map input Columns to OLE DB Command Transformation Parameters for Delete Operation


Script used in Video: How to use OLE DB Command Transformation for Delete Operation


USE [Test]
GO

CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NULL,
    [RegionCode] [varchar](100) NULL
)

Select * From [dbo].[Customer]


Delete from [dbo].[Customer]
where customerNAme=?
and REgionCode=?



Delete Records from a Table by using OLE DB Command Transformation in Data Flow Task in SSIS Package



  Related Posts / Videos on OLE DB Command Transformation 


Update Records in a Table by using OLE DB Command Transformation in SSIS Package - SQL Server Integration Services(SSIS) Tutorial


In this video we will learn How to perform Update Operation in OLE DB Command Transformation in SSIS Package.
We will learn the disadvantages of using OLE DB Command Transformation for Update Operation and what other options we have to perform Update in SSIS Package.

In this OLE DB Command Transformation Demo , we will learn

  1. How to Read data from Flat File Source in SSIS Package
  2. How to Create Table in SQL Server Database
  3. How to write Update statement with Input Parameter for OLE DB Command Transformation
  4. How to map input Columns to OLE DB Command Transformation Parameters for Update Operation


Script used in Video: How to use OLE DB Command Transformation for Update Operation


USE [Test]
GO
--Create Table for Update Records
CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NULL,
    [RegionCode] [varchar](100) NULL
)

Select * From [dbo].[Customer]

--Prepare Update Query for OLE DB Command Transformation in SSIS Package
Update dbo.Customer
set RegionCode=?
WHERE CustomerName=?




Update Records in SQL Server Table by using OLE DB Command Transformation in 
SSIS Package



  Related Posts / Videos on OLE DB Command Transformation 


How to use OLE DB Command Transformation for Insert Operation in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

In this video we will learn How to perform Insert Operation in OLE DB Command Transformation in SSIS Package.
We will learn the disadvantages of using OLE DB Command Transformation for Insert Operation and what other options we have to perform Insert in SSIS Package.

In this OLE DB Command Transformation Demo , we will learn

  1. How to Read data from Flat File Source in SSIS Package
  2. How to Create Table in SQL Server Database
  3. How to write Insert statement with Input Parameter for OLE DB Command Transformation
  4. How to map input Columns to OLE DB Command Transformation Parameters


Script used in Video: How to use OLE DB Command Transformation for Insert Operation

--Create Table in SQL Server Database for Load 
CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NULL,
    [RegionCode] [varchar](100) NULL
)

Select * From [dbo].[Customer]


--Prepare Statement for OLE DB Command Transformation
insert into dbo.Customer (CustomerName,RegionCode)
Values (?,?)



How to write insert statement with Parameters in OLE DB Command Transformation in SSIS Package



  Related Posts / Videos on OLE DB Command Transformation 


How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters - SQL Server Integration Services(SSIS) Tutorial

In this video we will learn how to run Multiple Stored Procedures in single Execute SQL Task and map their parameters to different variables.

You will be able to learn following Items from this video
  1. How to create an SSIS Package from Scratch
  2. How to create variables in SSIS Package
  3. How to create Sample Stored Procedures with input parameters
  4. How to map Stored Procedures input Parameters to variables in Execute SQL Task


Script to Create Sample Stored Procedures with Input Parameters

use Test
go
--drop table [dbo].[Customer]
CREATE TABLE [dbo].[Customer](
    [CustomerID] [int] identity(1,1),
    [CustomerName] [varchar](100) NULL,
    [RegionCode] [varchar](100) NULL
)

Select * from [dbo].[Customer]


Create procedure dbo.InsertCustomer_EU
@RegionCode VARCHAR(100)
as
BEGIN
Insert into dbo.Customer ( CustomerName,RegionCode)
values ('Aamir',@RegionCode)
end


Create procedure dbo.InsertCustomer_NA
@RegionCode VARCHAR(100)
as
BEGIN
Insert into dbo.Customer ( CustomerName,RegionCode)
values ('Aamir',@RegionCode)
end




Execute Multiple Stored Procedure in Execute SQL Task with Input Parameters in SSIS Package



 Related Posts / Videos on Execute SQL Task 

Using Variable in IF Clause in Execute SQL Task in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario:

How to run the different parts of SQL Statements depending upon the values of variable. Depending upon the value of variable we will be executing different parts of SQL Statements inside Execute SQL Task.

In this video of learning about Execute SQL Task , we will learn following Items

  • How to Create SSIS package from Scratch
  • How to create an variable in SSIS Package
  • How to use variable in expressions to build sql statement in Execute SQL Task.
  • How to use variable to run specific part of SQL inside Execute SQL Task



Expressions used in the Video to build IF clause by using Variable values from SSIS Package

"DECLARE @RegionCode VARCHAR(2)
SET @RegionCode='"+ @[User::RegionCD]+"'

If @RegionCode='EU'
BEGIN
insert into dbo.Customer_EU
Select * from dbo.Customer_NA

END

If @RegionCode='NA'
BEGIN
Truncate table dbo.Customer_NA
END
 "



How to use Variable in IF clause in Execute SQL Task in SSIS Package



 Related Posts / Videos on Execute SQL Task 

Find out which Stored Procedure is Currently Executing in Execute SQL Task in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario:

Let's consider that we are running multiple Stored Procedure by Execute SQL Task in SSIS Package. We want to know which Stored Procedure is currently running? Also we want to capture the time taken by each of the Stored Procedure to Execute?

Items you will leaven in this video:

  • How to Create an SSIS Package from Scratch
  • How to Create Stored Procedure in TSQL 
  • How to Log information in Audit Table Before and After Execution of Stored Procedure.
  • How to Call Multiple Stored Procedures in Execute SQL Task in SSIS Package
  • How to use Scope_identity() in TSQL

Script to Log Stored Procedure Starttime and End Time from Execute SQL task in SSIS Package

--Create Table to Load Stored Procedure Execution Information
create table dbo.LogSPExecution
( id int identity(1,1),
SPName VARCHAR(100),
StartTime datetime,
EndTime datetime)

go

--Create Couple of Sample Stored Procedures
Create procedure dbo.usp_SP1
as
BEGIN
WAITFor Delay '00:00:10'
END


Create procedure dbo.usp_SP2
as
BEGIN
WAITFor Delay '00:00:15'
END


--Script to use inside Execute SQL Task to Log Information
Declare @id int
insert into dbo.LogSPExecution( SPName,StartTime)
values ('dbo.usp_SP1',getdate())

Select @id=SCOPE_IDENTITY()
EXEC dbo.usp_SP1

update dbo.LogSPExecution
set EndTime=getdate()
where id=@id

GO

Declare @id int
insert into dbo.LogSPExecution( SPName,StartTime)
values ('dbo.usp_SP2',getdate())

Select @id=SCOPE_IDENTITY()
EXEC dbo.usp_SP2

update dbo.LogSPExecution
set EndTime=getdate()
where id=@id



How to get Execution information and Execution Time for Multiple Stored Procedures used in Execute SQL Task in SSIS Package

How to Save Query in Variable and Use in Execute SQL Task - SSIS Tutorial

Scenario:

Let's consider, we have a Flat file which contains the tables those need to be truncate before we load them again. We need to create an SSIS Package that can read the list of Table Names with schema and then Truncate them in given Database in SSIS Package. Once they are Truncated, we can have Data Flow Tasks to load them. But in this package we will be only building the Part how to Truncate them and the list of Tables can change anytime.


In this video we will learn how to save the query in a variable and then use in Execute SQL Task.

In this video you will learn following items
  1. How to Create SSIS Package from Scratch
  2. How to read list of Tables from a Flat File those need to be truncated from SQL Server Database
  3. How to Create String Type Variables in SSIS Package
  4. How to Write Expression on Variable to built SQL Statement
  5. How to use SQL Query ( Dynamic SQL) Saved in Variable in Execute SQL Task



How to build Query in expressions and Save into a Variable and Use Variable in Execute SQL Task as SQL Statement Source in SSIS Package



How to Return Deleted and Update Record Count from Execute SQL Task and Write to Flat File in SSIS Package - SQL Server Integration Services ( SSIS) Tutorial

Scenario:

We want to create an SSIS Package in which we will be using a Stored Procedure, which will delete and update some records and return us the delete and update record count. Once the Stored Procedure is completed, we want to log the record count to a file file for our reference. We will append the record to flat file.


In this video we will learn how to returned Number of records deleted or updated by a Stored Procedure that we called in Execute SQL Task.

You will be able to learn following items from this video demo


  1. How to Create SSIS Package from Scratch
  2. How to create variables to hold the values from Execute SQL Task
  3. How to Create Stored Procedure that will return Delete and Update Record Count
  4. How to Map Store Procedure output to variables in SSIS Package
  5. How to Write Variable values to Flat File

Script to get Deleted and Update Record Count from Execute SQL Task
use test
go
Select * from dbo.Customer_AS
Select * from dbo.Customer_NA


--drop procedure dbo.usp_UpdateCustomer
Create procedure dbo.usp_UpdateCustomer
AS
BEGIN

Declare @UpdateCnt INT
Declare @DeleteCnt INT

--Update Records in Customer_AS
update CAS
set CAS.LastName=CNA.LastName
from dbo.Customer_AS CAS
inner join dbo.Customer_NA CNA
on CAS.ID=CNA.ID

SET @UpdateCnt=@@ROWCOUNT

Delete from dbo.Customer_AS
where FirstName in ( Select FirstName from dbo.Customer_NA)
SET @DeleteCnt =@@ROWCOUNT

Select @UpdateCnt as UpdateCnt,@DeleteCnt AS DeleteCnt
END


Get Delete/Update Record Count from Execute SQL Task and Write to Flat file in SSIS Package



 Related Posts / Videos on Execute SQL Task 


Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

In this scenario we want to handle a situation in which we might not get the results from our query that we have used in Execute SQL Task in SSIS Package. It is very common error that we get as we have use Result Set to Single Row  and if our query does not return the results, we get an error in SSIS Package. Instead of error. We just want to stop the process with success.


Script used in the video to handle the situation when Execute SQL Task ResultSet is Set to Single Row and Query does not Return Row.
 
 Create Table dbo.FilesToLoad
  ( FolderPath VARCHAR(100),
  FileName VARCHAR(100)
  )
 
  insert into dbo.FilesToLoad
  values ('C:\Users\Aamir\Desktop\InputFolder','Customer_AS_20150425.txt')


Select FolderPath,FileName from dbo.FilesToLoad

Truncate table dbo.FilesToLoad

If exists(
Select FolderPath,FileName from dbo.FilesToLoad)
BEGIN
Select FolderPath,FileName from dbo.FilesToLoad
END
ELSE
BEGIN
SELECT 'Not Exists' as FolderPath,'Not Exists' AS  FileName
END




Single Row result set is specified, but no rows were returned in Execute SQL Task - SSIS Package



 Related Posts / Videos on Execute SQL Task 

How to Reject Already Loaded Files and Load Only New Files in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario: 

We get the files from our client that we need to load on daily basis to SQL Server Table. Sometime there are chances that the client might provide the same file again. In that case we want to move that file to Rejected Folder and don't want to load. If any new file will be received in the folder, we will load that after confirming the file is not loaded already.

What you will learn in this video

  1. How to read the files from Folder by using For-each Loop Container
  2. How to Log the file name into the SQL Server Table for validation
  3. How to Check if file is already loaded. You will build your query in Execute SQL Task by using Expressions
  4. How to use File System Task to move file from Source To Reject Folder in SSIS Package
  5. Move the new file to Archive folder after loading first time by using File System Task.


Script used in the video to handle already loaded files to move to RejectFolder and Load New Files in SSIS Package

--Create FileLoadStatus Table
CREATE TABLE [dbo].[FileLoadStatus](
 [ID] [int] IDENTITY(1,1) ,
 [FileName] [varchar](100),
 LoadDateTime datetime default getdate())


--Base Script to Check if File is already loaded or not
DECLARE @FileName VARCHAR(100)
SET @FileName='CustomerFile.txt'
IF EXISTS (SELECT 1
           FROM   [dbo].[FileLoadStatus]
           WHERE  filename =@FileName)
  BEGIN
      SELECT 1 AS FileExistsFlg
  END
ELSE
BEGIN
Select 0 AS FileExistsFlg
END



How to Archive Already Loaded Files and Load New Files To SQL Server Table in SSIS Package


How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package - SQL Server Integration Services ( SSIS ) Tutorial

Scenario:

We have multiple folders inside our main folder but for our SSIS Package we want to load only files from some specific folders which are assigned to that SSIS Package. The folder paths are saved with SSIS Package Name in a Table. We want to Extract the Folder Paths and then load the files to a SQL Server Table in SSIS Package.

What you will learn in this SSIS Package

  1. How to create a Table to store Folder Path and SSIS Package Name
  2. How to Extract Required records (Folder Paths) in SSIS Package from a SQL Server Table by using Execute SQL Task
  3. Why to use Full Result Set in Execute SQL Task in SSIS Package
  4. How to Loop through Object Type variable by using For-each Loop Container and save value to variable
  5. Use nested For-each Loop to use the Folder path that was returned by outer For-each Loop Container.
  6. How to use Variable value in Flat File Connection Manager by using Expressions
  7. How to Archive Files after loading to SQL Server Table in SSIS Package

Script used in the video to save folder path to SQL Table for Loading

USE [Test]
GO


--drop table [dbo].[FilesToLoad]
CREATE TABLE [dbo].[FilesToLoad](
    [PackageName] [varchar](100) NULL,
    [FolderOath] [varchar](100) NULL
)

insert into dbo.FilesToLoad
values ('Pkg_ASia_Europe_FileLoad','C:\Users\Aamir\Desktop\InputFolder\Customer_AS'),
('Pkg_ASia_Europe_FileLoad','C:\Users\Aamir\Desktop\InputFolder\Customer_EU')

Select * from [dbo].[FilesToLoad]




How to Load Files from Folders which paths are saved in SQL Server Table in SSIS Package



 Related Posts / Videos on Execute SQL Task 

Checklist for Migration of SQL Server 2008 to SQL Server 2014 - SQL Server DBA Tutorial

In this video you will learn overview of Migration checklist items, documents are attached in the description, you can use this checklist if you have requirement to migrate your SQL Server 2008 databases to SQL Server 2014 using backup/Restore method. It covers 95% of the important checklist items that you absolutely have to complete in order to avoid any migration issues. This is standard checklist, if you need to add items according to your environment, please do so. Video explains the importance of each item in the checklist.

Click Here to Download Checklist for Migration of SQL Server from 2008R2 to SQL Server 2014

Steps to Migrate SQL Server 2008 to SQL Server 2014 - SQL Server DBA Tutorial




Differences between inplace & Parallel or side by side Migration in SQL Server - SQL Server DBA Tutorial

In this video you will learn what are the differences between in-place and parallel or side by side upgrade/migration in SQL server. It goes through a table showing detail of important differences between in place and side by side upgrade of SQL Server, after going through this difference table, you will be able to decide whether it is good idea to do in-place upgrade or parallel upgrade of your SQL Server Production environment.

 Difference between In-place and Parallel Migration Document Download Link

What is the difference between In-place and Parallel Migration - SQL Server DBA Tutorial