Posts Tagged ‘SSIS’

I honestly don’t remember that I need to run SQL Server Management Studio (SSMS) as Administrator to connect to the Integration Server 2008R2. However, this has changed in both SSIS 2012 and SSIS 2014.

I have a laptop running on Windows 7 and SSIS 2014. I need to run SSMS as Administrator to connect to the Integration Server 2014. Why? Below is the error that I get when trying to connect as me, even though I am part of the Administrators local group.

SSIS 2012 and 2014 access error via SSMS

To resolve this issue on Window 7:

1. You must be part of the Distributed COM Users group.

Go to Control Panel > User Accounts > Manage User Accounts  and then select the Advanced tab. Under the Advanced User Management, click on the Advanced button. Under the Local Users and Groups, select Groups and then select Distributed COM Users group. Add your username or Users group into this group.

Add user into Distributed COM Users group

2. Allow remote connection to the Integration Service on DCOM Config.

Go to Control Panel > Administration Tools > Component Services. Navigate to Computer folder > My Computer >DCOM Config. Locate either MsDtsServer100 (for SSIS 2008R2) or Microsoft SQL Integration Services 11.0 (for SSIS 2012) or Microsoft SQL Integration Services 12.0 (for SSIS 2014).

Right click on the item and then select Properties. Select the Location tab and tick the “Run application on this computer” option.

DCOM Config - enable running the application on this computer

3. Select the Security tab and then under the Launch and Activation Permissions, select the Customize option and then select the Edit button.

DCOM Config - allow remote access

Add your username or user group and then tick local launch and local activation. Tick remote launch and remote activation if the server is located on a different server.

DCOM Config - Allow local launch and local activation

3. Select the Security tab and then under the Access Permissions, select the Customize option and then select the Edit button. Add your username or user group and then tick local access and remote access.

DCOM Config - Allow local access and remote access

4. Restart the SSIS service (this is very important as changes will not be applied without a restart).

5. Test connecting to the SSIS via SSMS.

6. Note: Connecting to SSRS via SSMS will work too once the above configuration is applied.

Read Full Post »

I have an SSIS package that is set up to only send an email when the variable of String type has values. If this variable is empty, then don’t execute the SendMail task.

I first used the following precedence constraint expressions, which check if the FinalResults variable is not empty. The FinalResults contains the email message. So when it has values, send the email out, otherwise, do nothing.

@[User::FinalResults] != “”
ISNULL(@[User::FinalResults]) == FALSE

precedence constraint in string

Unfortunately, the above expressions don’t work. I resolved to change my stored procedure to obtain the number of records returned. When there is no value, the number of records will be zero. I then use this as my precedence constraint instead. This has worked perfectly 🙂

@[User::TotalRecords] > 0

precedence constraint in integer

Read Full Post »

SendMail task only supports rending the email message in plain text. Adding CHAR(13) into the message in SQL only works if you are using Office365 email client (where the default body text formatting is in HTML). When this message is open in Outlook, it’s rendered in plain text.

I need to display an email message that looks like below to list each vessel as a separate line.

From: target365@email.com

Sent: Wednesday, 26 November 2014 11:59:56 AM (UTC+08:00) Perth
To: chinchin@email.com

Subject: Target 365 Achievement on 4/10/2012

Vessel A has achieved a Perfect Year
Vessel B has achieved a Perfect Quarter
Vessel C has achieved a Perfect Year

As you see from the syntax below, I have added CHAR(13). This CHAR(13) will be rendered in &#x0D ;, which needs to be replaced back with CHAR(13) to get the correct result.

— Email content (variation 1)

SELECT Results = REPLACE(Results, ‘&#x0D ;’, CHAR(13))



                  (  SELECT ClassValue + ‘ has achieved a ‘ + Reward + CHAR(13)

                     FROM #Ranking

                    WHERE RewardDate = CAST(@Date AS Date)

                    AND Ranking = 1

                    FOR XML PATH ( )

                 )  AS Results

 FROM #Ranking

 WHERE RewardDate = CAST(@Date AS Date)

 AND Ranking = 1

) sub

In order to have it rendered in HTML in the normal Outlook, the SendMail task no longer can be used. A Script task is used instead. To use the Script task, the above SQL needs to be modified to include the html tag of  <br />. The message needs to contain this tag. Again, it is first rendered in &lt;br /&gt; which then replaced back with <br />  to get the correct result.

— Email content (variation 2)

SELECT Results = REPLACE(Results, ‘&lt;br /&gt;’, ‘ <br /> ‘)



                  ( SELECT ClassValue + ‘ has achieved a ‘ + Reward + ‘<br />’

                     FROM #Ranking

                     WHERE RewardDate = CAST(@Date AS Date)

                    AND Ranking = 1

                    FOR XML PATH ( )

                  ) AS Results

 FROM #Ranking

 WHERE RewardDate = CAST(@Date AS Date)

 AND Ranking = 1

) sub

Next, create 4 variables in the SSIS package i.e. SendMailFrom, SendMailTo, SendMailSubject and SendMailMessage. SendMail variables

Next, edit the Script task and assign the 4 variables as read-only. Script Task for SendMail read only variables

Next, edit the C# code of the Script task add

using System.Net.Mail;

and then add the code below into the Main function:

public void Main() {

// Storing SSIS variables in .Net variables.

String SendMailFrom = Dts.Variables[“SendMailFrom”].Value.ToString();

String SendMailTo = Dts.Variables[“SendMailTo”].Value.ToString();

String SendMailSubject = Dts.Variables[“SendMailSubject”].Value.ToString();

String SendMailBody = Dts.Variables[“SendMailBody”].Value.ToString();

// Get SMTP Server from SMTP Connection Manager.

String SmtpServer = Dts.Connections[“SMTP”].Properties[“SmtpServer”].GetValue(Dts.Connections[“SMTP”]).ToString();

// Create an email and change the format to HTML

MailMessage myHtmlFormattedMail = new MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody); myHtmlFormattedMail.IsBodyHtml = true;

// Create a SMTP client to send the email

SmtpClient mySmtpClient = new SmtpClient(SmtpServer);


// Close Script Task with success

Dts.TaskResult = (int)ScriptResults.Success;


Build the code and then test it.

My SSIS packages using SendMail task and Script task are shown below.

Using SendMail task (email message is always rendered in plain text): Send Mail in Plaint Text

Using Script task (to render the email message in HTML): Send Mail in HTML

Read Full Post »

%d bloggers like this: