Feeds:
Posts
Comments

Archive for November, 2014

I have an SSIS package that gets today’s date and then stores it into a variable of date data type. I then have another Execute SQL Tasks that takes in this date variable value and returns with some values. When the date is set to 2012-10-04 (4 October 2012), somehow it gets interpreted as 2012-04-10 (10 April 2012). This of course will return incorrect output.

To overcome this, I change the variable data type from date to string and its SQL statement to below so that it will have this format e.g. Nov 27 2014  3:12PM.

SELECT CONVERT(VARCHAR, CAST(GETDATE() AS DATETIME), 100) AS [Date]

This has worked like a charm! 🙂

Advertisements

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])
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))

FROM

(  SELECT DISTINCT

                  (  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 /> ‘)

FROM

( SELECT DISTINCT

                  ( 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);

mySmtpClient.Send(myHtmlFormattedMail);

// 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 »

Once I have SQL Server 2014 installed successfully on my laptop, I went directly to configure the Data Quality Service (DQS) i.e. create catalog i.e. SSISDB database. I then went to configure the Master Data Service (MDS) without making sure that I have all the pre-requisites installed, which was the IIS components.

So I ran the MDS Configuration Manager, I had a green tick on the Windows Power Shell but a yellow exclamation on the IIS. I then navigated to the Database Configuration to create a new database called MDS. Next, I navigated to the Web Configuration, it said I have not got IIS installed. So I went to “Programs and Features” > “Turn Windows Features on and off” to install the required components for IIS. I ticked all components under IIS except for IIS 6 Scripting Tools and IIS 6 WMI Compatibility and FTP server. This then requires a reboot. Once rebooted, I ran the MDS Configuration Manager and I still had a yellow exclamation on the IIS under pre-requisites. This was odd. I thought I need to create a website first. So I created a Default Website and called it MDS. The website was created successfully but it threw an error complaining about ASP.NET not installed. ASP.NET was indeed installed. This time I resolved to Google. One blog listed out that I needed to add extra features below: Add the following features: – .NET Framework 3.0 Features – WCF Activation HTTP Activation Non-HTTP Activation I then navigated to check if those features above were installed. They weren’t. So installed them and restarted the IIS service (just in case). This time it threw a different error below: Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list I had no idea what the error means so I Googled again. One blog suggested that I needed to re-register my asp.net. C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -I This indeed has resolved my issues. My MDS has now been configured properly 🙂 MDS

Read Full Post »

%d bloggers like this: