SSIS: Script task to copy files from one location to another location

Script task to copy files from one folder to another folder.

In SSIS, we can use file system task to copy and move files from one folder to another folder.

However you can accomplish same task using script task.  Here's how you can do.



/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_fbfcdb1278d0459f9a8c35e0b0ab16de.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 // This is where my source files are located.
        string sourceDir = @"C:\Data\";
// This is where my target files will be copied
        string target = @"C:\Archive\";
     

        public void Main()
        {
            string[] sDirFiles = Directory.GetFiles(sourceDir);
            if (sDirFiles.Length > 0)
            {
                foreach (string file in sDirFiles)
                {
                    //string[] splitFile = file.Split('\\');
                    string copyFile = Path.GetFileName(file);
                    string source = sourceDir + "\\" + copyFile;
                    Copy(source, target + "\\" + copyFile);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public void Copy(string source, string target)
        {
            try
            {
                //If file exists at the target location, then delete it
                if (File.Exists(target))
                {
                    File.Delete(target);
                }
                //Otherwise copy it from source to target
                File.Copy(source, target);
            }
            catch (Exception ee)
            {

            }
        }
    }
}

Restoring Database from one environment to another environment in SQL Server

As a DBA, we are required to restored database from one environment to another environment all the time.

The following steps are involved in restoring database.

Step 1. Find the mdf and ldf files to restore
Step 2. Do Actual Restore of the database
Step 3 Find orphan object for user login and fix them. ( This is important as different environment have different users --Production version test or dev environment)

Step 1.

Find the files to restore .



USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,      @Backup VARCHAR(128)
       ,      @Filename VARCHAR(128)
       ,      @dbname VARCHAR(128)

-- Set Variables
SET @Path     = 'C\BackupFiles' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1'  ---Name of Database ---replace with your


-- Restore FileList
RESTORE FILELISTONLY
FROM DISK = @Filename

Step 2

In real environment, you may have replication going on the server where you want to restore. So we have to make sure that we kill replication.


:connect SQLTST
:setvar dbname "Development1"
:setvar delay "00:00:00.003"
:setvar max_retries "5"
:r "C\Scripts\UtilityScripts\Recovery\KillDB.sql"

USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,    @Backup VARCHAR(128)
       ,    @Filename VARCHAR(128)
       ,    @dbname VARCHAR(128)

   -- Set Variables
SET @Path     = 'C\Backup' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1' --Which Database to restore

RESTORE DATABASE @dbname
FROM   DISK = @Filename
WITH   MOVE 'Development1' TO 'C:\SQL\Development1_data.mdf' ---Replace with Phyiscal Name value which was given in first step.  If there are multiple mdf file
,      MOVE 'Development1_log       TO 'C:\SQL\Development1_log.ldf'
,      STATS = 5
,       replace -- May be needed if replacing existing database.


Step 3:

Fix orphan object

EXECUTE sp_change_users_login 'Report'


This will give a list of username where the objects are orphan. For example if a user 'Home' exist


Then run this

EXECUTE sys.sp_change_users_login 'Auto_Fix', 'Home'

SSIS: script task to write to file and send as email attachment

Problem:

Create a package which read a query and save the result in file and send that file as an attachment.

Solution:

The problem says that we do a select query on some database, save the result to a file and send them as attachment. Or you asked to write to file and leave it there. This file can be picked by any other process. In this case, I am going to send the file as an attachment through email.

Designing the package:

This package will consists of three task:
1. Execute SQL Task
2.  Script Task
3. Send Mail Task

Variables:

Lets create all the variables needed for this package.

1. varResultSet: object type. This variable will hold the result of our execute SQL task.
2. varNewFileDelimiter: This variable is used as file delimiter which will be used in script task.
3. varUploadDirectoryPath: This variable is used to save my file created and hold values where I will be saving my file.
4.varMyResultFileName: This variable hold file name. Set Expression value to true and under expression add following.
@[User::varUploadDirectoryPath]+ "\\"+"MyResult"  + "_" + Right("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + Right("0" + (DT_WSTR,2)DAY(GETDATE()),2)  + (DT_WSTR,4)YEAR(GETDATE()) + ".csv"

Here I am saving my file as csv file with date added to file name.

5. varExceptionCount: This variable hold any exception counts.

Most of these variable will be used in script task and send mail task.

So lets go and add Execute SQL task to our package.

1. Execute SQL Task

Added connection manager which will be used. Right down your query in your sql statement and set ResultSet to Full Result Set.

Under Result set add the variable
Result Name = 0
Variable =varResultSet

This is all we have to do in the first task. Now if you execute this task, the result of your query will be held in this variable.

Let's add script task to consume this result and write to file.


2.  Script Task

In our script task, let's chose C# language and Under Read Only add following variables.

User::varResultSet,User::varMyResultFileName,User::varNewFileDelimiter

And In ReadWriteVariable, add User::varExceptionCount

Open your script task and add following code. Make sure that if you are using different variable name, change them at your end.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;  
using System.IO;
using System.Reflection;

namespace ST_7cdb67cf97304adab75a69c0b1d4688e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion



        public void Main()
        {
            try
            {
                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                A.Fill(dt, Dts.Variables["User::varResultSet"].Value);
                string filepath = Dts.Variables["User::varMyResultFileName"].Value.ToString();
               
                int i = 0;

                Dts.Variables["User::varExceptionCount"].Value = dt.Rows.Count;

                if (dt.Rows.Count > 0)

                {
                    StreamWriter sw = null;

                    sw = new StreamWriter(filepath, false);

                    for (i = 0; i <dt.Columns.Count; i++)
                        {
                   
                            if (i == dt.Columns.Count -1)
                        {
                            sw.Write(dt.Columns[i].ToString());
                        }
                    else
                        {
                            sw.Write(dt.Columns[i].ToString()+Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                        }
                    }
               sw.WriteLine();

                    foreach (DataRow row in dt.Rows)
                    {
                        object[] array = row.ItemArray;

                        for (i = 0; i < array.Length; i++)
                        {
                            if (i == array.Length - 1)
                            {
                                sw.Write(array[i].ToString());
                            }
                            else
                            {
                                sw.Write(array[i].ToString() + Dts.Variables["User::varNewFileDelimiter"].Value.ToString());
                            }
                        }
                        sw.WriteLine();
                    }
                    sw.Close();

            }
                    }
                        catch (Exception ex)
                        {
                         }
                 }
    }
}
       

Save this. Now if you run your package, you find your file in the folder.

3. Send Mail Task

Add a send mail task, add your connection manage, add email id where you want to send. In expression, select FileAttachment and set it your variable (in this case @[User::vvarMyResultFileName]

You are all set to send this to as attachment.