How to filter – reject records during Microsoft SQL Server Integration Services (SSIS) import of a flat file

If you want to filter – reject records during a Microsoft SQL Server Integration Services (SSIS) import of a flat file, you can use the Conditional Split Data Flow Transformation. If the flat file is a CSV file with the following contents:

 

Flat file contents

EEEtest,0,1,2

000000,0,1,2

blabla test,0,1,2

 

image

 

Result

EEEtest,0,1,2

blabla test,0,1,2

 

One row is filtered, because the first column has a value of “000000” and thus starts with “0000”, see the Condition expression

 

Complete dataflow screendump

image

How to calculate the difference in seconds between 2 datetime strings (yyyyMMdd HHmmss) in a SSIS expression

If you want to calculate the difference in seconds between 2 datetime strings (yyyyMMdd HHmmss) in a SSIS expression, you can use the following expression:

 

Pre

@[User::StartDateTimeString] = “20100420 100000”

@[User::EndDateTimeString] = “20100420 101010”

 

Expression

DATEDIFF("ss",(DT_DBTIMESTAMP)(SUBSTRING(@[User::StartDateTimeString],5,2) + "/" + SUBSTRING(@[User::StartDateTimeString],7,2) + "/" + SUBSTRING(@[User::StartDateTimeString],1,4) + " " + SUBSTRING(@[User::StartDateTimeString],10,2) + ":" + SUBSTRING(@[User::StartDateTimeString],12,2) + ":" + SUBSTRING(@[User::StartDateTimeString],14,2)),(DT_DBTIMESTAMP)(SUBSTRING(@[User::EndDateTimeString],5,2) + "/" + SUBSTRING(@[User::EndDateTimeString],7,2) + "/" + SUBSTRING(@[User::EndDateTimeString],1,4) + " " + SUBSTRING(@[User::EndDateTimeString],10,2) + ":" + SUBSTRING(@[User::EndDateTimeString],12,2) + ":" + SUBSTRING(@[User::EndDateTimeString],14,2)))

 

Result

610

 

 

image

How to add a new column to a flat file connection preserving the existing column mappings in Microsoft SQL Server Integration Services

If you created a flat file connection in Microsoft SQL Server Integration Services and the structure of the input files changed (a column is added), you can add a column to you’re flat file connection, by following the steps below. This will preserve the existing column mappings and column renames, it will only add a column at the end.

  • Double click on you’re flat file connection, this will open the “Flat File Connection Manager Editor”

image

  • Click on the New button
  • This will add a new column at the end, now you can change the Name, ColumnDelimiter, OutputColumnWidth etc.

 

 

Note: The ColumnDelimiter value of the last column, should be the same as the RowDelimiter

Because mine input file is a CSV file, with rows delimited by newlines and columns delimited by commas, I expected the ColumnDelimter of each column to be a comma, but when you try to change the ColumnDelimiter of the last field to a comma, you will get the exception: “The row delimiter cannot be the same as the column delimiter.” This is because the last ColumnDelimiter should be the same as the row delimiter. The last column of each record will be followed by the row delimiter and not a ColumnDelimiter.

 

image

How to add a “not null” column to a table using t-sql alter table statement in Microsoft SQL Server

There are two options for adding a “not null” column to a table using t-sql alter table statement in Microsoft SQL Server.

  • Add a default to the new column, which sets the value of existing records to a not null value
  • Add the column as a “null” column, set the value of existing records then change the column to a “not null” column

Both have there pros and cons, using a default may impact performance but is less code. using the other approach might be better for production performance but is more code and the new column must be supplied by inserts.

 

 

Using a default

 

use MyDatabase
go -- Add the column "MyColumn1" to table "MyTable" as "NOT NULL"
if exists (select 1 from sys.objects where name = 'MyTable' and type = 'U') 
begin 
if not exists (select 1 from sys.columns where name = 'MyColumn1' and Object_ID = Object_ID('MyTable')) 
 begin 
alter table MyTable with nocheck add MyColumn1 int not null default(0 end
end go

 

 

 

Adding as “null”, then change to “not null”

use MyDatabase go -- Add the column "MyColumn2" to table "MyTable" as "NULL" if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable')) begin if not exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable')) begin
alter table
MyTable with nocheck add MyColumn2 int null end end go

-- Change value of existing records
if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable'))
begin
if
exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable') and is_nullable = 1)
begin
update MyTable set MyColumn2 = 0
end
end
go




-- Change "NULL" column to "NOT NULL" column
if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable'))
begin
if
exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable') and is_nullable = 1)
begin
alter table MyTable alter column MyColumn2 int not null
end
end
go

 

 

Note

If you’re table has a schema name use Object_ID(‘MySchema.MyTable’)

 

 

MSDN documentation

NULL / NOT NULL: Specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified. A new column added to a table must either allow null values, or the column must be specified with a default value.

http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx

Showing bmp, png, jpg or gif images as buttons in WPF in the best quality

If you want to show a bmp, png, jpg or gif image as a button in WPF in the best quality, you can use the following XAML:

 

XAML

<Button  HorizontalAlignment="Left" Margin="312,45,0,0" Name="refreshButton" VerticalAlignment="Top" Width="60" Height="45" Cursor="Hand" Click="button1_Click">
<Image Source="/Ada.Tip.WpfUserControls;component/Images/SmallSync.png" Stretch="None" />
</Button>

 

* The Stretch property of the Image is set to “None”, so the image is shown in it’s original dimensions. If you let WPF scale the image you might not get the best quality. I use Paint .NET to scale the image to the correct size and then show the image in it’s original dimensions in WPF.

* Ada.Tip.WpfUserControls is the name of my Visual Studio 2010 project and assembly name.

 

Add you’re image to the project and it will automatically be converted to a resource entry in you’re assembly

 

image

 

Result (Refresh button)

image

A little command line parser in C#

If you create a  custom C# console application and want it to use parameters, you must create a command line parser to covert the command line parameters to C# variables.

There are many different ways to pas parameters to a custom C# console application like:

  • MyConsoleApp.exe –Parameter1Name “Parameter1Value” –Parameter2Name “Parameter2Value”
  • MyConsoleApp.exe “Parameter1Value”,”Parameter2Value”
  • MyConsoleApp.exe Parameter1Name=”Parameter1Value” Parameter2Name=”Parameter2Value”
  • etc.

 

In mine C# console applicaiton I used the format MyConsoleApp.exe Parameter1Name=”Parameter1Value” Parameter2Name=”Parameter2Value”.

To get a specific parameter from the args array, you can use:

 

Usage

var parser = new CommandLineParser();
string parameter1Value = parser.GetValue<string>("Parameter1Name", args);

 

 

Class

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
namespace ADAICT
{
public class CommandLineParser
{
private const string _keyValueSeperator = "=";
public T GetValue<T>(string key, string[] args)
{
if(string.IsNullOrEmpty(key))
{
throw new NullReferenceException("Parameter [key] can't be null or empty");
}
if (args == null)
{
throw new NullReferenceException("Parameter [args] can't be null or empty");
}
// Select all keyvalue pairs with the given key
var keyValuePairs = from a in args
where !string.IsNullOrEmpty(a) && a.Trim().StartsWith(key + "=")
select a.Trim();
if (keyValuePairs.Count() > 0)
{
// Get last keyvalue pair with the given key
var lastKeyValuePair = keyValuePairs.Last();
// Get the value from the last keyvalue pair with the given key
var value = this.GetValueFromKeyValuePair(lastKeyValuePair);
// Convert the value to the return type
var converter = TypeDescriptor.GetConverter(typeof(T));
return (T)converter.ConvertFromString(value);
}
else
{
throw new Exception(string.Format("The key [{0}] can't be found on the commandline, make sure it is supplied on the commandline and there are no spaces between the key and the equalsign. Key is case sensitive!",key));
}
}
public string GetValueFromKeyValuePair(string keyValuePair)
{
var result = string.Empty;
if(!string.IsNullOrEmpty(keyValuePair))
{
// Split line on "="
string[] keyValue = keyValuePair.Split(new string[] { _keyValueSeperator }, StringSplitOptions.RemoveEmptyEntries);
if (keyValue.Length >= 2)
{
// Restore the "=" in the value
result = String.Join(_keyValueSeperator, keyValue, 1, keyValue.Length - 1);
}
else
{
if (keyValue.Length == 1)
{
// value does not contain a "="
result = keyValue[0];
}
}
// Remove leading and trailing quotes
result = result.Trim(new char[] {'"'});
}
return result;
}
}
}

How to convert a text containing a datetime in format “yyyyMMdd_HHmmss” to a datetime in C#

If you want to convert a text containing a datetime in format “yyyyMMdd_HHmmss” to a datetime vaiable in C#, use the following code:

 

UnitTest

string dateTimeFormat = "yyyyMMdd_HHmmss";
string textContainingDateTime = string.Format("This is some text containing a datetime 20100808_080808 in the format [{0}]", dateTimeFormat);
DateTime result = DateTime.Now;
DateTime.TryParseExact(textContainingDateTime.Substring(40, dateTimeFormat.Length), dateTimeFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out result);
DateTime expectedResult = new DateTime(2010,8,8,8,8,8);
Assert.AreEqual(expectedResult, result);

 

Result

Passed

TFS 2010 Best Practices and TFS Structure

We used the TFS Best Practices Guide on CodePlex for deploying and using our TFS server: http://tfsguide.codeplex.com/

 

TeamDevGuide.gif

 

Based on this guide we used the following TFS Structure:

 

$MyTeamProject1

/Main                                                                                    à Can contain solution (.sln) files

/Source

/MyApp1                                              à Contains MyApp1.sln file

/ClassLibrary1                       à Contains ClassLibrary1.csproj

/MyApp1Web                       à Contains Default.aspx

/ClassLibrary1Tests              à Contains test project and code

/MyApp1WebTests              à Contains test project and code

/MyApp2                                              à Contains MyApp2.sln file

/ClassLibrary2                      à Contains ClassLibrary1.csproj

/MyApp2Web                       à Contains Default.aspx

/ClassLibrary2Tests              à Contains test project and code

/MyApp2WebTests              à Contains test project and code

/SharedBinaries                                     à Shared binaries e.g. libraries

/SharedSource                                       à Shared source code

/Docs                                                                    à Contains product documentation

/Tests                                                                    à Container for tests

/FunctionalTests

/PerformanceTests

/SecurityTests

/TeamBuildTypes                                                                  à Created automatically by Team Build.

/BuildType1

/BuildType2

 

How to serialize and deserialize an object in C# to a XML file

The following code shows how to serialize and deserialize an object in C# to a XML file.

 

Code

using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml.Serialization;
using System.Reflection;
namespace MobileUI.BC
{
public class SerializationHelper<T> where T : class, new()
{
private string _fileName;
public string FileName
{
get
{
if (string.IsNullOrEmpty(_fileName))
{
_fileName = "Data.xml";
}
return _fileName;
}
set
{
_fileName = value;
}
}
private T _data;
public T Data
{
get
{
if (_data == null)
{
_data = new T();
}
return _data;
}
set
{
_data = value;
}
}
public void Save()
{
string filePath = Path.Combine(GetExecutionPath(), FileName);
using (TextWriter writer = new StreamWriter(filePath))
{
XmlSerializer xs = new XmlSerializer(typeof(T));
xs.Serialize(writer, this.Data);
}
}
public T Load()
{
string filePath = Path.Combine(GetExecutionPath(), FileName);
using (TextReader reader = new StreamReader(filePath))
{
XmlSerializer xs = new XmlSerializer(typeof(T));
this.Data = xs.Deserialize(reader) as T;
}
return this.Data;
}
public static string GetExecutionPath()
{
string codeBase = Assembly.GetExecutingAssembly().GetName().CodeBase;
return new Uri(Path.GetDirectoryName(codeBase)).LocalPath;
}
}
}

 

Example

        [TestMethod]
public void SaveTest()
{
Data data = new Data();
data.Days = 2;
SerializationHelper<Data> sh = new SerializationHelper<Data>();
sh.Data = data;
sh.Save();
data.Days = 3;
data = sh.Load();
Console.WriteLine(data.Days);
}

 

Result

2