Send List of Files from A Folder as Email by using Send Mail Task- SQL Server Integration Services (SSIS) Tutorial

Scenario:

We receive different files in one of the folder. Before we start our loading process, we have a requirement to send all the file names in the email to our team by using SSIS Package.

Solution:

In this video we will be able to solve above scenario, we will be using following components of SSIS Package 

  1. How to Create Parameters for Folder Path, EmailTo and EmailFrom
  2. How to use For-each Loop Container to loop through all the file in a folder
  3. How to use ReadOnlyVariables and ReadWrite Variables in Script Task
  4. How to create list of list and save into a variable
  5. How to use Expressions in Send Mail Task for MessageSource( Body), LineTo and FromLine


Code used in the Script 
Dts.Variables["User::FileNamesList"].Value = Dts.Variables["User::FileNamesList"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString() + "\r\n";


Expressions used in the Video

Subject :
@[System::PackageName]+ " Package completed successfully in Prod"

Expression for Message Source:
"Hi Team
Please find the list of File name in Input Folder \n "+
 @[User::FileNamesList]+"

Thank you
Tech Brothers"



How to Email file names from a Folder by using Send Mail Task in SSIS Package - SSIS Tutorial



  Related Posts/Videos on Send Mail Task  

Create a file with Date From SQL Server Table and Send as Attachment in SSIS Package - SQL Server Integration Services(SSIS) Package

Scenario:

We need to create a file with Date on daily basis and then send to our client in email by using SSIS Package.

Solution:

In this video we will learn how to solve above scenario, we will learn following components
How to create Parameters in SSIS Package
How to use Data Flow Task and read data from SQL Server Table and load to Flat file 
How to write expression to create file with Date
How to use expression on Flat file Connection Manager in SSIS Package
How to Send a file as attachment by using Send Mail Task in SSIS Package
How to use Expressions in Send Mail Task in SSIS Package

Expression used in the video
@[$Package::OutputFolder]+"Customer_"+Replace(Substring((DT_WSTR,30)getdate(),1,10),"-","")+".txt"


How to Create a File with Date everyday and Send from an SSIS Package by using Send Mail Task



  Related Posts/Videos on Send Mail Task  


How to Configure Send Mail Task and Send Email in SSIS Package - SQL Server Integration Services(SSIS) Package

In this video you will learn how to use Send Mail Task and explore different options we have in Send Mail Task in SSIS Package.
You will learn how to create SMTP Server and use in Send Mail Task.
How to use Static values in Send Mail task for FromLine,ToLine,Subject, Body etc.



How to Send An Email from SSIS Package by using Send Mail Task - SSIS Tutorial



  Related Posts/Videos on Send Mail Task  

Attach Multiple Files Dynamically in Send Mail Task in SSIS Package - SQL Server Integration Services(SSIS) Package

Scenario:

We have multiple files in one of the folder, we need to create an SSIS Package that can attach all the files from that folder and send to required recipients.

Solution:

In this video we will learn how to send multiple files as attachment in SSIS Package by using Send Mail Task. We will also learn below components as part of solution

  1. How to Create SSIS Package Parameters
  2. How to use Foreach Loop Container to Loop through Files in a Folder
  3. How to use Variables in SSIS Package to build attachment list
  4. How to use Send Mail Task in SSIS To send Multiple File Dynamically
  5. How to use Expressions in Send Mail Task in SSIS Package
Code used in the Script Task in this video


Dts.Variables["User::Attachment"].Value = Dts.Variables["User::Attachment"].Value +
Dts.Variables["User::FileFullPath"].Value.ToString() + "|";




How to send Multiple Files as Attachment by using Send Mail Task in SSIS Package - SSIS Tutorial



  Related Posts/Videos on Send Mail Task  




Send Error Email with Error Code, Error Description by using Send Mail Task in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We have created an SSIS Package, that fails sometime. The only way to find out if the Package is failed to check SQL Server Job history or alerts generated by SQL Server Agent Job. We want to include components in SSIS Package so we can send an Email from SSIS Package with Error Information when the Package fail.


Solution:

In this video we will find out the solution for above scenario, we will learn below items in this Video
How to create SSIS Package Parameters 
How to use Event Handlers in SSIS Package for OnError Events
How to Create error in SSIS Package for Test Purpose
How to use Send Mail Task in Event Handler to send email with Error Code, Error Description etc.


Expressions used in the Send Mail Task MessageSource

" Hi Team
The Package "+ @[System::PackageName] + " has failed in "+ @[$Package::Environment]+" with below error
Error Code: "+ (DT_WSTR,50)@[System::ErrorCode]+"
Error Description: "+ @[System::ErrorDescription] +"
Error Source: "+ @[System::SourceName]+"

Please contact SSIS Team if have any questions
Thank you
Tech Brothers"



How to Create 100's of Sample Databases by Script in SQL Server - SQL Server Tutorial

Scenario:

Let's say you just started with fresh Installation of SQL Server Instance. Now you want to create some sample database so you can use for different purposes. You don't want to run Create Database Script 100's of time and there is possibility you want to Data and Log location different than by default. 

Solution:

The below script can be used to create databases as many as you want. You have the options to provide the Data Directory and Log directory in which you want to create the database. You can choose the part of Database Name and then it will add the numbers to it. 



--Create Variables
Declare @DataFilePath VARCHAR(100)
Declare @LogFilePath VARCHAR(100)
Declare @SubPartDBName VARCHAR(100)
Declare @StartCnt int
Declare @MaxDBCnt int

--Set the Variable Values, @MaxDBCnt is Number of Databases you want to Create
SET @StartCnt=1
SET @MaxDBCnt=101

--Provide the Data File Path And Log File Path
SET @DataFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\'
SET @LogFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\'
--Chose the First part of your DB name, Let's say TEST is chosen then Databae will be created Test1,Test2....Test100
SET @SubPartDBName='Test'


--Create Databases
While ( @startCnt<@MaxDBCnt)
BEGIN

Print CAst(@startCnt AS VARCHAR(100))
DECLARE @DBFullName VARCHAR(500) =@SubPartDBName+CAST(@StartCnt AS VARCHAR(10))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL= 'CREATE DATABASE ['+@DBFullName+']

 ON 
( NAME = N'''+@DBFullName+''', FILENAME = N'''+@DataFilePath+@DBFullName+'.mdf'' ,
 SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'''+@DBFullName+'_log'', FILENAME = N'''+@LogFilePath+@DBFullName+'_log.ldf'' ,
 SIZE = 1024KB , FILEGROWTH = 10%)'
SET @startCnt=@startCnt+1
Print @SQL
Execute (@SQL)
END

Create Zip File with Date and Keep adding files to it after loading for the same day in SSIS Package - SQL Server Integration Services(SSIS) Package

Scenario:

We get multiple files during a day and night. we have scheduled to run our SSIS Package every hour. We want to create a Zip file per day and then keep adding the files to it after loading in the same day. Next day a new Zip file should be automatically created and files should be copied to that after loading.


Solution:

In this video we will learn the solution for our scenario " How to Create A Zip File with Date Per day and add files to it after loading in SSIS Package". Below are the list of items we will learn in this video


  1. How to Create Package Parameters for InputFolder and ArchFolder so we can change the values of them according to the environment ( QA, UAT, Prod) and we don't have to make any changes in the SSIS Package. 
  2. How to use Foreach Loop Container to look through files in SSIS Package
  3. Save File name in FileName Variable From Foreacah Loop Container
  4. How to Change .NET Framework 4 to 4.5
  5. How to add reference to Assemblies such as System.IO.Compression and System.IO.Compression.FileSystem
  6. Create empty Zip File by using Script Task
  7. Add file to Zip file by using Script task in SSIS Package


Script used in Script Task to Create Zip file with Date and Add Files to Zip File in SSIS Package


public void Main()
        {


            //Assign values to local variable from SSIS Package Parameters and Variables
            string zipfile = Dts.Variables["User::ArchFullPath"].Value.ToString();
            string ArchFolder = Dts.Variables["$Package::ArchFolder"].Value.ToString();
            string filename = Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString() + filename;


            //If zip File already exist for the same day, just add the files to it
            if (
            File.Exists(zipfile))
            {
                MessageBox.Show(" Zip File does Exists");
                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                    File.Delete(inputfilepath);
                }

                //If zip file does not exist for the day, create it and add files to it
            }
            else
            {
                MessageBox.Show("File Does not exists");
                var fileStream = new FileStream(zipfile, FileMode.Create);
                fileStream.Close();

                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                    File.Delete(inputfilepath);
                }

            }


            Dts.TaskResult = (int)ScriptResults.Success;
        }

Expressions for ArchFullPath Variable:
@[$Package::ArchFolder]+Replace(Substring((DT_WSTR,30)GEtdate(),1,10),"-","_")+".zip"






How to Zip ( Compress) Multiple Folders and Delete in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario: 

We have a lot of folders in one of our Source Folder, we want to zip all of them to Archive folder. Each Folder will be zipped to its own file. Once the folders are zipped we want to delete them from Source Folder.

Solution:

To perform this requirement, we will be using Script task. Here are the things that you will learn in this video.


  1. How to create SSIS Package Parameters for SourceFolder and ArchFolder paths
  2. How to Map the Package Parameters to Script Task in SSIS Package
  3. How to change .NET Framework from 4. to 4.5 version in Script Task in SSIS Package
  4. How to Add reference to assemblies such as System.IO.Compression and System.IO.Compression.FileSystem
  5. Loop through folder names one by one by using Foreach Loop inside Script Task and Zip them and delete them.


Script used in the Script task to Zip Multiple Folders in SSIS Package

public void Main()
        {
            //Assign values to local variable from Package Parameters
            string inputfolder = Dts.Variables["$Package::SourceFolder"].Value.ToString();
            string archfolder = Dts.Variables["$Package::ArchFolder"].Value.ToString();
        

           //Loop through folders and zip them and delete them from Source folder
            foreach (string dir in Directory.GetDirectories(inputfolder))
            {
                //MessageBox.Show(s);
              //get only folder name form full path
                string foldername = dir.Replace(inputfolder, "");
               //Zip the input folder to file
                ZipFile.CreateFromDirectory(dir, archfolder+foldername+".zip");
               //Delete the folder
                Directory.Delete(dir,true);
              
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }




 Zip Multiple Folders to Separate Zip Files and then Delete from Source Directory in SSIS Package


Zip or Compress Files according to the Name of Files in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We have one Source folder in which everybody puts the files to load. There are Customer Files those start with Customer_Date_Time, There are Sales files with Sales_DateTime etc.  We want to create an SSIS Package that can run at the end of the day and zip the files according to the names of the files.

Solution:

  1. Create Package Parameters for SourceFolder and ArchFolder so the path can be changed by using SSIS configuration according to the environment
  2. Create FileName variable so we can save file name with extension when loop through files by using  Foreach Loop Container 
  3. Create ZipFile Variable and write expression to build Full Zip File path, Here is the expression that we used in the video @[$Package::ArchFolder] +Substring( @[User::FileName] ,1,FindString( @[User::FileName],"_",1))+Replace(Substring((DT_WSTR,30)Getdate(),1,10),"-","_")+".zip"
  4. Change .Net Framework to 4.5 version in script task so we can add assemblies such as System.IO.Compression and System.IO.Compression.FileSystem
  5. Write Script to Create new Zip file if not exists according to name part and date. Then add the file to it and delete the file from source folder.
  6. If zip file already exists then add the file to it and delete the source file from Source Folder.

Script used in the Script task to create Zip File according to the name part of files in source folder.

public void Main()
        {

            //Assign values to local variable from SSIS Package Parameters and Variables
            string zipfile = Dts.Variables["User::ZipFile"].Value.ToString();
            string filename= Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString() + filename;


            //If zip File already exist for the same day, just add the files to it
            if (
            File.Exists(zipfile))
            {
                //MessageBox.Show(" Zip File does Exists");
                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                    
                    //Delete the file after zipping it
                    File.Delete(inputfilepath);
                }
               
                //If zip file does not exist for the day, create it and add files to it
            }
            else
            {
               // MessageBox.Show("File Does not exists");
                var fileStream = new FileStream(zipfile, FileMode.Create);
                fileStream.Close();

                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                }

                //Delete the file after zipping it
                File.Delete(inputfilepath);
               
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

How to Zip/Compress the files according to the name part in SSIS Package - SSIS Tutorial




    Related Posts/Videos on Zip / UnZip by Script Task  

Zip ( Compress) Files and Add them to Folder According to Extension - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We have different types of files in one of our folder. There are Excel files, CSV files, Text Files,BMP files. We want to Zip them according to the Extension. All the excel files should be Zipped to XLSX_Date. Once the file is zip, it should be deleted from the source folder.

Package should be able to handle any type of file automatically and zip them with Date if added to the source folder.


Solution:

To solve this requirement we will be using below Tasks in SSIS Package step by step
  1. Create Package Parameters for SourceFolder and ArchFolder so the path can be changed by using SSIS configuration according to the environment
  2. Create FileName variable so we can save file name with extension when loop through files by using  Foreach Loop Container 
  3. Create ZipFile Variable and write expression to build Full Zip File path, Here is the expression that we used in the video  @[$Package::ArchFolder] +Replace(Substring( @[User::FileName] ,FindString( @[User::FileName],".",1) ,5),".","")+"_"+Replace(Substring((DT_WSTR,30)GEtdate(),1,10),"-","_")+".zip"
  4. Change .Net Framework to 4.5 version in script task so we can add assemblies such as System.IO.Compression and System.IO.Compression.FileSystem
  5. Write Script to Create new Zip file if not exists according to extension and date. Then add the file to it and delete the file from source folder.
  6. If zip file already exists then add the file to it and delete the source file from Source Folder.


How to Zip files according to Extension of File Code used in the Script Task

public void Main()
        {

            //Assign values to local variable from SSIS Package Parameters and Variables
            string zipfile = Dts.Variables["User::ZipFile"].Value.ToString();
            string filename= Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString() + filename;


            //If zip File already exist for the same day, just add the files to it
            if (
            File.Exists(zipfile))
            {
                //MessageBox.Show(" Zip File does Exists");
                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                    
                    //Delete the file after zipping it
                    File.Delete(inputfilepath);
                }
               
                //If zip file does not exist for the day, create it and add files to it
            }
            else
            {
               // MessageBox.Show("File Does not exists");
                var fileStream = new FileStream(zipfile, FileMode.Create);
                fileStream.Close();

                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                }

                //Delete the file after zipping it
                File.Delete(inputfilepath);
               
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }



SSIS Tutorial - Compress different types of Files to Different Zip files as per Extension of File in SSIS Package



    Related Posts/Videos on Zip / UnZip by Script Task  

Load File, Zip it and Delete from Source Folder in SSIS Package- SQL Server Integration Services(SSIS) Tutorial

Scenario:

We get multiple files in our Input Folder, we want to load those files to SQL Server Table and then zip each of them individually and loading to table.

Solution:

In this video we will learn step by step how to load multiple files and archive each of them once loaded to the table. We will learn following items in this video


  1. How to read multiple Files from Input Folder by using Foreach Loop Container
  2. How to use Data Flow Task to load Text files to SQL Server Table
  3. How to Create Package Parameters for input Folder and Archive Folder 
  4. How to create FileName variable so we can use in Foreach Loop Container to save file name on each iteration
  5. Use Package Parameters and Variable in Script Task to Create Zip File and then add source file to it
  6. How to use Script Task to Delete Loaded file
  7. How to change .NET Framework version in Script task from 4 to 4.5 so we can add assemblies such as
  8. System.IO.Compression and System.IO.Compression.FileSystem 
Script used in the video to zip and delete each file individually



public void Main()
        {


            //set the value to local variables
            string filename = Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString()+filename;
            string archpath = Dts.Variables["$Package::ArchFolder"].Value.ToString() +
                   filename.Replace(".txt", ".zip");


            //Create Zip File and then Add file to it
            using (ZipArchive zip = ZipFile.Open(archpath, ZipArchiveMode.Create))
            {
                zip.CreateEntryFromFile(inputfilepath, filename);
            }

            //Delete the file that is compressed and load to sql table
            File.Delete(inputfilepath);
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }


Get File names from Zip Files and Insert into SQL Server Table - SQL Server Integration Services(SSIS) Tutorial

Scenario:

There are tons of zip files in our Archive folder. we need to  know how many files each zip file contains and names of them. How can we do that in SSIS Package?


Solution:

There can be multiple ways to do this. I thought about using Foreah Loop Container first and then Script Task. Then thought why not to use only Script Task. So here is the things we will be doing in this SSIS Package to get this requirement done.


  1. Create an Package Parameter for ArchiveFolder Path
  2. Inside Script task, we will change the  Dot Net Framework to 4.5 version so we can make sure of System.IO.Compression and System.IO.Compression.FileSytem Assemblies.
  3. Create ADO.NET Connection Manager to the database where we want to log the zip file name and file names inside the zip file.
  4. Use the ADO.NET Connection Manager in script task to insert records into table those we retired from File System for Zip Files.

SQL Server Table Script to Store Zip File Names and files inside them


Create table dbo.ZipFileInfo ( 

id int identity(1,1),
ZipFileName VARCHAR(100),
FileName VARCHAR(100))
go


How to Get File Names for All Zip Files in a Folder in SSIS Package and write them to Table, Script used in Script Task

public void Main()
        {
            //Assign values to local variable from Package Parameters
            string archfolder = Dts.Variables["$Package::ArchFolder"].Value.ToString();

            //Sure ADO.NET Connectionn Manager that we created in SSIS Package
            SqlConnection DBConn = new SqlConnection();
            DBConn = (SqlConnection)(Dts.Connections["DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
            //Prepare SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = DBConn;


            //Get List of all Zip Files from Archive Folder
            string[] files = Directory.GetFiles(archfolder,"*.zip");

            // Display all the files.
            foreach (string zipfile in files)
            {
                 using (ZipArchive archive = ZipFile.OpenRead(zipfile))
                {
                    foreach (ZipArchiveEntry FileEntry in archive.Entries)
                    {
                        sqlCmd.CommandText = "Insert into dbo.ZipFileInfo Values('"
                        zipfile + "','" + FileEntry.FullName + "')";
                       // MessageBox.Show(sqlCmd.CommandText);

                        sqlCmd.ExecuteNonQuery();
                        //MessageBox.Show(zipfile+":::"+FileEntry.FullName);
                    }
                } 
            }
            DBConn.Close();

            Dts.TaskResult = (int)ScriptResults.Success;
        }



Extract File Names from Zip Files and Insert into SQL Server Table in SSIS Package - SSIS Tutorial

AlwaysOn Availability Group on SQL Server Failover Instances Installed in Cluster Mode - SQL Server DBA Tutorial

Scenario:


This video provides a solution to the scenario where it is required to have local availability for SQL Server instances between the nodes on one location i.e. Charlotte - and provides AlwaysOn availability Disaster Recover on another location i.e. NYC.

Solution:

In this video you will learn following:

1- Overview of 4 node cluster configuration
2- How to setup failover between Two SQL Server instances between 2 nodes out of 4 node cluster
3- How to setup Availability group between SQL Server instances installed on 4 different nodes for DR scenario
4- Limitations of Availability group setup in 4 node cluster environment
5- How to failover AlwaysOn Availability between two replicas
6- How to create DR scenario for AlwaysOn Availability groups


Fig 1: 4 Node Cluster with SQL Server Cluster Always On Availability Group Setup



Setting up AlwaysOn Availability Group on SQL Server Instances Installed in Cluster Mode in 4 Nodes Windows Cluster

Setup AlwaysOn Availability group when SQL Server instances are installed in Cluster Mode - SQL Server DBA Tutorial

In this video you will learn following:

Scenario: 


Setting up AlwaysOn Availability Group between SQL Server instances installed in cluster mode on Two node cluster. In this video you will learn following:
1- Overview of existing Two nodes cluster
2- Failover cluster configuration of existing SQL Server instances installed on both nodes in cluster mode
3- Step by step Creating Availability group
4- Resolutions to the below errors during Availability group setup
5- Limitations of Availability group when SQL Server instance is installed in cluster mode
6- Configuring Cluster to compensate Availability Group setup
7- Limitation of Windows Failover Cluster in 2 nodes setup for Availability group

Step by Step Configuration and Resolution of Availability Group in 2 nodes cluster:


Check out the videos to follow step by step configuration and resolution of below errors during successful Availability group when SQL Server instances are installed in cluster mode

Error 1 (If both SQL Server instances are on ONE node)

SQL Server instance TBSSQLUAT\SQLUAT is installed on the same computer as server instance TBSSQLCLUSTER\SQLPROD, which is already selected for this availability group. Each Windows Server Failover Clustering (WSFC) node can host only one availability replica per availability group. Specify a server instance on a different WSFC node. (Microsoft.SqlServer.Management.HadrTasks)

Important Warning:

Summary for the replica hosted by TBSSQLUAT\SQLUAT

Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover (with possible data loss).

Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.

Readable secondary: Yes
In the secondary role, this availability replica will allow all connections for read access, including connections running with older clients.

Error 2 : (Using Normal Wizard with Default selection)

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
ADDITIONAL INFORMATION:
The following folder locations do not exist on the server instance that hosts secondary replica TBSSQLUAT\SQLUAT:
F:\MSSQL11.SQLPROD\MSSQL\DATA; L:\MSSQL11.SQLPROD\MSSQL\Data;
 (Microsoft.SqlServer.Management.HadrTasks)

Error 3 : (After Passing the AG validation)

Create failed for Availability Group 'AG_TEST_SQLClusterMode'.  (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to create, join or add replica to availability group 'AG_TEST_SQLClusterMode', because node 'TBSNODE1' is a possible owner for both replica 'TBSSQLCLUSTER\SQLPROD' and 'TBSSQLUAT\SQLUAT'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)

Error 4:

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database 'SalesOrder_New' to the availability group 'AG_TEST_SQLClusterMode' on the availability replica 'TBSSQLUAT\SQLUAT'. (Microsoft.SqlServer.Smo)
Database "SalesOrder_New" is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using WITH NORECOVERY. (Microsoft SQL Server, Error: 1464)

Error 5:

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database 'SalesOrder_New' to the availability group 'AGTest_SQLinclustermode' on the availability replica 'TBSSQLUAT\SQLUAT'. (Microsoft.SqlServer.Smo)

The mirror database, "SalesOrder_New", has insufficient transaction log data to preserve the log backup chain of the principal database.  This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)



Setup AlwaysOn Availability Group When SQL Server Installed in Cluster Mode Part1



Setup AlwaysOn Availability Group When SQL Server Installed in Cluster Mode Part2

How to Setup Peer To Peer Replication in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to setup Peer To Peer Transactional replication in SQL Server, video covers the following topics:
1- Best practices of Peer To Peer SQL Server Replication
2- What are the pre-requisites of Peer To Peer replication
3- How to configure distribution on SQL Server
4- How to create Publication in SQL Server Replication
5- Best practices of replication account in SQL Server
6- Limitations of Peer to Peer Replication in SQL Server



Setup Peer to Peer Replication in SQL Server - SQL Server DBA Tutorial

Extract Files From Zip Files and Delete the Zip Files once UnZiped in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

On daily basis we get zip files those can contain single file or more than one files of different types. We want to create an SSIS Package that can loop through all zip files and extract the files from them and save them to a folder from where we can load them to different destinations in SSIS Package.

Solution:

In this video we will learn the solution of our given scenario and create an SSIS Package. Below is the list of items we will cover in this video


  1. How to Loop through zip files in SSIS Package
  2. How to create Package Parameters for Zip and UnZip folders and use them in Script Task
  3. How to Change Dot Net Framework from 4 to 4.5 in Script Task
  4. How to add Assemblies such as System.IO.Compression and System.IO.Compression.FileSystem in Script Task
  5. How to Extract Files from Zip file to folder and then delete the zip file in SSIS Package by using Script Task

Script used in the video to Unzip the Zip files 

 public void Main()
        {

            string zipfullpath = Dts.Variables["User::ZipFullPath"].Value.ToString();
            string inputfolder = Dts.Variables["$Package::InputFolder"].Value.ToString();

         using (ZipArchive arch= ZipFile.OpenRead(zipfullpath))
            {
                foreach(ZipArchiveEntry entry in arch.Entries)
                {
                    entry.ExtractToFile(Path.Combine(inputfolder,entry.FullName));
                }
            }
            File.Delete(zipfullpath);
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }




Load Text Files,Zip them to Folder with Datetime and Delete From Input Directory in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We get text files in our Input Folder and we need to run our SSIS Package multiple times a day and load those files to table/s and then zip those files in a file with datetime in Archive Folder.

Solution:


We will be using .NET Framework 4.5 and ZipFile class to handle this scenario by using in Script Task in SSIS Package.

Here are the items you will learn in this video

  1. How to read multiple Text files with same metadata and load to SQL Server Table, we will make use of Foreach Loop Container to loop through files
  2. How to Create Package Parameters in SSIS Package for Input Folder and Archive folder path 
  3. How to use Package Parameters in Script Task
  4. How to change .NET Framework in Script task from 4 to 4.5 so we can use ZipFile Class
  5. How to Add reference to Assembly System.IO.Compression.FileSystem
  6. How to Zip the Files to folder with Datetime and then delete them from input Folder in Script Task

Script used in the Script task to Zip the files to Zip File with Datetime and delete from Input Folder


public void Main()
        {
            //set the value to local variables
            string inputfolder=Dts.Variables["$Package::InputFolder"].Value.ToString();
            string archpath = Dts.Variables["$Package::ArchFolder"].Value.ToString() + 
"Sales_" + DateTime.Now.ToString("yyyyMMddHHmmss")+".zip";
           
            //Create Zip File with Datetime and put all the files from input folder
            ZipFile.CreateFromDirectory(inputfolder, archpath);

            //Delete all the files from Input Folder
            DirectoryInfo Dir = new DirectoryInfo(inputfolder);

            foreach (FileInfo File in Dir.GetFiles())
            {
                File.Delete();
            }
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }

Load Text/ CSV files To table when Number of Column Can Change in Source File/s - SQL Server Integration Services(SSIS) Tutorial

Scenario:

I have been reading this question so many times on different forums, blog post etc. " How to load the data to SQL Server Table from File when Columns can change in the source file. Sometime we have less number of columns than Table columns or sometime same number of columns. 


Let's move to this scenario today.

Solution

Step 1:

To make the SSIS Package more dynamic, I am going to use three Parameters, you can use variables if you are still using old versions of SSIS such as 2005 or 2008/R2.

Fig 1: Create SSIS Package Parameters or Variable so you can change when you need

As you can see that we have Source Folder parameter, Table name, Delimiter. So you can use this package to load any files from Source Folder.You only have to provide the delimiter of file and table name in which you want to load the file/s.


Step 2:

Click in Connection Manager Pane and then Create ADO.NET Connection by providing Server Name and database Name. After creating I have renamed it to DBConn.

Fig 2: Create ADO.NET Connection so we can use in Script Task


Step 3: 

Bring the Script Task to Control Flow Pane and then open it. I have used C# for scripting. Map the SSIS Variables/Package Parameters as shown below.

Fig 3: Map the SSIS Variables/Package Parameters to Script task in SSIS Package

Step 4: 
Click on Edit Script Button and then add below script to Script Task.

Under namespaces add these two namespaces

using System.Data.SqlClient;
using System.IO;

Replace public void Main with below script. There is a lot more you can do. You can even check the column name exists before you insert and handle that exception. In this code, I am not checking if column name exists or not. So if somebody provide a file with column name which do not exits in table, this script is going to fail.

public void Main()
        {

            string delimiter = Dts.Variables["$Package::Delimiter"].Value.ToString();
            string TableName = Dts.Variables["$Package::TableName"].Value.ToString();
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)
         (Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
           
            
            //Reading file names one by one
            string SourceDirectory = Dts.Variables["$Package::SourceFolder"].Value.ToString();
            string[] fileEntries = Directory.GetFiles(SourceDirectory);
            foreach (string fileName in fileEntries)
            {
                
               // MessageBox.Show(fileName);
                string columname = "";
                
                //Reading first line of each file and assign to variable
                System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);


                
                //Writing Data of File Into Table
                int counter = 0;
                string line;

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {

                    if (counter == 0)
                    {
                        columname = line.ToString();
                        columname = "" + columname.Replace(delimiter, ",") ;
                        //MessageBox.Show(columname);
                      }

                    else
                    {
                       // MessageBox.Show("Inside ELSE");
                        string query = "Insert into " + TableName +
           "(" + columname + ") VALUES('" + line.Replace(delimiter, "','") + "')";
                        //MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                        myCommand1.ExecuteNonQuery();
                    }

                    counter++;

                }

                SourceFile.Close();
            }
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }



Save the script and close the Script Task Editor. Now you can go ahead and place files in Source directory with different columns. Provide the Table name in which you want to load those files in SSIS Package. 

Once you will deploy this SSIS Package, you will be using Configuration to change the values for TableName,Delimiter and Source Folder.