Transfer Users from one SQL Server to another including SID’s the easy way

Before I wrote an article regarding this one but it seem that was the hard way of doing it.  See the article here http://anyrest.wordpress.com/2012/10/11/how-to-synchronize-loginsusers-between-mirrored-servers/ , but recently I was playing with SSIS in Visual Studio and found out that there is an easy way of doing it without complicated scripting and can all be achieved in a matter of 4 easy steps.

This will be really helpful for those Mirroring a lot of databases and synchronizing their users down to SID’s and permissions.  For this to happen you need Visual Studio with Business Intelligence Project Templates attached to it.  Let’s start.

1. Create an Integration Services project 

2. Use the Transfer Login task

3. Setup your Connection

4. Set up the Transfer task properties

In this section you since you are mirroring the users credentials from the database its best to use only “AllLoginsFromSelectedDatabases” and choose the databases you need to copy the logins from.

For Mirroring it is important to set “CopySid” to “true” so your application will run seamless without encountering an access denied issue as it will copy the users exactly how it is on the main SQL Server

see its really easy, why haven’t I used it like this before

Send Email from SSIS with option to indicate Email User and Password

If you had noticed the built in Send Email task from the SSIS package does not have the option of indicating a user name and password, it will only authenticate using Windows Authentication, well that’s not good specially if you have an email provider that does not use that authentication model like Gmail.

To solve that issue we need a bit if coding so you will have the functionality to add an Email Server you like with User Credentials in runtime, thankfully SSIS have the Script Task which we can use for a lot of reasons, adding more flexibility to the already powerful SSIS tool. With script task you can extend in developing using VB.Net or C#.Net

So this will be our solution developing our own Send Email Function with option for User Credentials.
Now lets start.
You only need 2 main things 1 is the variables and another one is the script.

First - To declare variables in SSIS. Here are the minimum variables you might need for your custom send email function.

Second – After creating those variables you then need to create your script, open the script task editor by double clicking on the icon you may already have on the designer

and it will open a script task editor window now click edit script, this will fire up the Visual Studio Scripting Task. So if you are a .Net developer this will be familiar.

Now to access your variables inside the Script all you have to do 2 steps, make it visible to your script which I will explain later and referencing to the script like this:

Dts.Variables["YourVariableName"].Value.ToString()

Now all you have to do is develop your Sending Email functionality and here is how I have done mine:

public void Main()
{
    string sSubject = "Test Subject";
    string sBody = "Test Message";
    int iPriority = 2;

    if (SendMail(sSubject, sBody, iPriority))
    {
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
        //Fails the Task
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

public bool SendMail(string sSubject, string sMessage, int iPriority)
{
    try
    {

        string sEmailServer = Dts.Variables["sEmailServer"].Value.ToString();
        string sEmailPort = Dts.Variables["sEmailPort"].Value.ToString();
        string sEmailUser = Dts.Variables["sEmailUser"].Value.ToString();
        string sEmailPassword = Dts.Variables["sEmailPassword"].Value.ToString();
        string sEmailSendTo = Dts.Variables["sEmailSendTo"].Value.ToString();
        string sEmailSendCC = Dts.Variables["sEmailSendCC"].Value.ToString();
        string sEmailSendFrom = Dts.Variables["sEmailSendFrom"].Value.ToString();
        string sEmailSendFromName = Dts.Variables["sEmailSendFromName"].Value.ToString();

        SmtpClient smtpClient = new SmtpClient();
        MailMessage message = new MailMessage();

        MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);

        //You can have multiple emails separated by ;
        string[] sEmailTo = Regex.Split(sEmailSendTo, ";");
        string[] sEmailCC = Regex.Split(sEmailSendCC, ";");
        int sEmailServerSMTP = int.Parse(sEmailPort);

        smtpClient.Host = sEmailServer;
        smtpClient.Port = sEmailServerSMTP;

        System.Net.NetworkCredential myCredentials = new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
        smtpClient.Credentials = myCredentials;

        message.From = fromAddress;

        if (sEmailTo != null)
        {
            for (int i = 0; i < sEmailTo.Length; ++i)
            {
                if (sEmailTo[i] != null && sEmailTo[i] != "")
                {
                    message.To.Add(sEmailTo[i]);
                }
            }
        }

        if (sEmailCC != null)
        {
            for (int i = 0; i < sEmailCC.Length; ++i)
            {
                if (sEmailCC[i] != null && sEmailCC[i] != "")
                {
                    message.To.Add(sEmailCC[i]);
                }
            }
        }

        switch (iPriority)
        {
            case 1:
                message.Priority = MailPriority.High;
                break;
            case 3:
                message.Priority = MailPriority.Low;
                break;
            default:
                message.Priority = MailPriority.Normal;
                break;
        }

        //You can enable this for Attachements.  SingleFile is a string variable for the file path.
        //foreach (string SingleFile in myFiles)
        //{
        //    Attachment myAttachment = new Attachment(SingleFile);
        //    message.Attachments.Add(myAttachment);
        //}

        message.Subject = sSubject;
        message.IsBodyHtml = true;
        message.Body = sMessage;

        smtpClient.Send(message);
        return true;
    }
    catch (Exception ex)
    {
        return false;
    }
}

Now on the top you need to add the following references

using System.Text.RegularExpressions;
using System.Net.Mail;

And that’s your code. That simple. Now all you have to do is making the variables visible to your script. Once you exit the Script Editor you go back to this window and tick the variables you want to use.

Deleting Files using File System Task in SSIS

I recently had noticed that deleting a file in SSIS using the File System Task is not straightforward specially when your file is not hardcoded in your package but it is declared as a variable. I just noticed it when I was creating a Foreach Loop Container using a File enumerator.

Inside that for loop is a Script Task which manipulates the file and a File System Task which now deletes when the whole process is done.

Initially I was using a variable to delete a file like this

But trust me you will encounter an error that will tell you that Variable “something” is used as a source or destination and is empty. I found another workaround by using expressions to come out with the filename but still I gives me some error to the likes of [File System Task] Error: An error occurred with the following error message: “The process cannot access the file ‘C:YourFile.txt’ because it is being used by another process.”.

So after a bit of playing around I found a solution the work around is a bit of pain but it works, First you have to create a Dummy Flat File Connection String by adding it to you connection manager. Give it a Name and point it to any text file with some content (so the OK button will enable and save it).

Now edit the properties of the Flat File Connection Manager, remove the Connection String and add a new expression using the connection string pointing to your File Variable which was outputted from the ForEach Loop Container

Here is how you output the variable in the For Each Loop

Now use that connection string in your File System Task, by making the Source Path Variable to false and Source Connection to the connection string you had just created.

The solution bypassed the Path Variable being True but the Connection String becomes now the dynamic variable.

[end]