Have you ever had to pad out a number or a string with zero’s or other characters? Ever needed to make sure a number has 3 leading zero’s, but you keep receiving 7 digit numbers?

The .Net framework has a handy method for this called PadLeft() and PadRight().

Example

1
2
3
4
5
6
7

string needsPadding = "12345";
 
//We Need This String To Have Leading 0's To Make 7 Digits
 
needsPadding = needsPadding.PadLeft(7, '0');
 
//needsPadding Now Equals - 0012345

Table variables are like a form of temporary table, except they’re more lightweight, and are easier to use, they also don’t require cleaning up and are generally faster across smaller datasets.

Here’s how to use them

1
2
3
4
5
6
7
8
9
10

  --Declare The Table And Columns
  DECLARE @Accounts TABLE
  (ShopID varchar(20),Amount decimal(20,0))
 
  --Insert Into
  INSERT INTO @Accounts
  SELECT TOP 100 ShopID, Amount FROM tblStatements
 
  --Select Out
  SELECT * FROM @Accounts

When scripting in SSIS sometimes you want to trigger your own custom error messages.
You may have an email task that triggers on your package wide OnError event.

This is done simply by calling the Dts.Events.FireError() method. Here’s an example of how to fire your own error.

1
2

Dts.Events.FireError(0, "Some Short Error Message", "Some Longer Error Message", string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;

Here’s a snippet that will show you what day of the week it is, ie Monday, Tuesday etc.

Just change the GETDATE() part to whatever date you want to show the day name for

1
2
3
4
5
6
7
8
9
10
11

SELECT 
  DayOfTheWeek =
  CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7
    WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 0 THEN 'Saturday'
END

Sometimes you need to just extract the date or the zero hour from a DateTime field. That is you may want to select everything that happened after 00:00 on a given day.

Here’s how

1

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Accessing ADO.Net Connection Managers from an SSIS script task / script component is pretty easy – you just need to cast the object returned from AcquireConnection() to the appropriate class (i.e. SqlConnection if you’re using SQL Native Client).

1

SqlConnection conn = (SqlConnection)Dts.Connections["yourConnectionManagerName"].AcquireConnection(null);

Often you may wish to make a column unique in a table, but not use it as the primary key.

Use this peice of SQL to modify your existing column to force it to contain only unique records.

1
2

ALTER TABLE tableName
ADD CONSTRAINT someCustomConstraintName UNIQUE (yourColumnName)

Occasionally you may find that you have two sets of data, that may look very similar and your trying to do a simple Merge Join on them, yet they will just not join.

There are no errors, and you end up putting data viewers on each output source to confirm that the columns do indeed match up, and of course they do, but you can’t figure out why they just won’t join.

The answer is to put a “Sort” transformation directly after each data set output, before it feeds into the Merge Join. You then just need to make sure you sort the data on a common field, eg if your trying to join to data sets on an “Account Number” field, then just sort two data sets by the Account Number field.

I scratched my head over this for a long time until I realized this little gotcha. SSIS doesn’t throw any errors so its hard to figure out why they won’t join.

We’ve all experienced the frustration of having a session expire on us, possibly losing your work because you needed to take a phone call, or you got side tracked by an email that you needed to get out quickly, only to return to a form you were filling in and have it log you out, losing all your past work.

This maybe isn’t such a big deal for day to day web sites, but when at work, using a system that you have open all the time and use all day every day, having a 20 minute session time out can be frustrating.

At work, we are more often then not using web based applications now, through our browsers, if this time out is set at something low, like 20 minutes, its very easy to lose work due to session time outs.
Refreshing the page will restart the time out, and clicking a button will restart the time out, but doing both of these means you lose any form data you might be entering, as records such as change or incident records can sometimes be long and painful to re-enter.

Using this small piece of script, you can keep your sessions alive, without the need to refresh. So how does it work?

The script inserts a full screen iFrame which loads your web site into it, you use the site just like normal, and it looks no different to how you would normally use it except for one thing. In the background there is a timer running, every 10 minutes this timer kicks in and inserts an invisible iFrame behind your main page, loads up your web site, then removes its self. This gives the effect of refreshing the page, except your not touching your main window.

Your free to work for as long as you want on a single form or page without the worry of the page expiring on you. The web site is being refreshed in the background from another frame every 10 minutes.

Simply change the variable named “url” in the top of this page, in the script block, then past the full page into a .html file and open with your browser. Host the file on your web server and send it around your office. No more time outs

** Note** The CSS included in the following example must be included in your page if you decide to create your own. This styling hides the keepAlive frame and makes the main frame fit to your screen size.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
<html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> 
<head>
<script type='text/javascript' src='https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js'></script> 
<script type="text/javascript"> 
$(document).ready(function(){    
//Replace This With Your Applications URL
var url = "http://yourwebapplicationhere.com";
//Insert The Main Frame
$('<iframe id="mainFrame" name="mainFrame" src="' + url +'"></iframe>').appendTo('body');
 
	window.setInterval(function() {
		//Remove Previous Keep Alive Frame
		if ($('#keepAliveFrame').length > 0) {
			$('#keepAliveFrame').remove();
		}	
		//Inject Keep Alive Frame
		$('<iframe id="keepAliveFrame" name="keepAliveFrame" src="' + url + '"></iframe>').appendTo('body');
	},600000);
 
	//Resize Frame If Need Be		
	$(window).resize(function() {
	  $('#mainFrame').css({'height': '100%', 'width' : '100%'});
	});
});
</script> 
<style type="text/css">
 
* {
  border: 0;
  padding: 0px;
  margin: 0px;
}
 
html {
	overflow: hidden;
}
 
html, body {
  height: 100%;
  width: 100%;
}
 
#keepAliveFrame {
  display: none;
  width: 10px;
  height: 10px;
}
 
#mainFrame {
  width: 100%;
  height: 100%;
}
 
</style>
</head>
<body>
 
</body>
</html>

When using Microsoft entity framework objects, you’ll often need to validate then when updating or inserting, although as these are designer generated classes, you can’t simply open the class file and add the attributes above the properties.

To add validation for entity objects, create a class file in your models folder, give it the namespace of your project and then create a partial class for the class you want to add validation for, we’ll then apply the [Metadatatype] attribute and specify a second class to inherit from.

Sounds a little confusing, lets break it down.

Lets say my Entity framework generated class is called  ”Report_Groupings”,  it has a property named “sys_id”  which corresponds to the “sys_id” field in my database table.
We want to add validation for this field so that, anything over 50 characters throws a validation error.

First off all create the class file, name it something like “Validation.cs”,  open the file and give it your project namespace etc.  Make sure to add “using” statements for:

• System.ComponentModel.DataAnnotations
• System.Web
• System.Web.Mvc

Now create a partial class for the Entity class you want to add validation for,  in my case its called “Report_Groupings”.
The important part here is to add the attribute [MetadataType(typeof(Report_Groupings_Validation))] above the partial class.
The “Report_Groupings_Validation” class doesn’t exist yet,    but we’ll add that next.   Obviously just give this a name that is relevant to your projects

So Far

1
2
3
4
5
6
7
8
9
10
11
12

using System.ComponentModel.DataAnnotations;
using System.Web;
using System.Web.Mvc;
 
namespace Report_Builder.Models
{
    [MetadataType(typeof(Report_Groupings_Validation))]
    public partial class Report_Groupings
    {
 
    }
}

Next, add another class below this named “Report_Groupings_Validation”  (Change this name to what ever you used in the previous Metadatatype attribute)

Its in this class that you’ll do your validation,  as you normally would had you not been using an entity framework class.

Example, I wish to add validation for the “sys_id” property on my Report_Groupings class,  so if I define a “sys_id” property in this validation class, it will be inherited due to the partial class we defined previously.

Here’s the final piece of code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

using System.ComponentModel.DataAnnotations;
using System.Web;
using System.Web.Mvc;
 
namespace Report_Builder.Models
{
    [MetadataType(typeof(Report_Groupings_Validation))]
    public partial class Report_Groupings
    {
 
    }
 
    public class Report_Groupings_Validation
    {
        [StringLength(50, ErrorMessage = "sys_id Must Be 50 Characters Or Less")]
        public string sys_id { get; set; }
    }
}

Now my model will not pass validation when the sys_id field is submitted with more than 50 characters.