SSIS Solving: Error loading ‘TestPackage 7.database’ : Deserialization failed: The method or operation is not implemented.

I was working on an old SSIS project in Microsoft SQL Server Business Intelligence Development Studio 2005 and when I opened a package I got the error:

Error loading ‘TestPackage 7.database’ : Deserialization failed: The method or operation is not implemented.

Solution

The folder containing the project file MyProject.dtproj, should contain only one *.database file, named exactly like the project name, like MyProject.database, remove all other *.database files and make sure, you’re versioning system does not contain any *.database files, exclude them from you’re versioning system.

SSIS error: Column "…" cannot be found or is not specified for query (13865)

When I refreshed a SSIS package OLE DB Source, I got the error: column "test" cannot be found or is not specified for query (13865).

This was caused by the fact, that the query contained a column that did not exist anymore in de database.

Removing the column name in the SqlCommand resolved this error.

SSIS Flat File Source Error: 0xC020200E Cannot open the datafile and 0xC004701A failed the pre-execute phase

I was getting an error when importing a csv file, by using the SSIS Flat File Source.

 

Error

Error: 0xC020200E at Read Flat File [18]: Cannot open the datafile "".
Error: 0xC004701A at Import File, SSIS.Pipeline: component "Read Flat File" (18) failed the pre-execute phase and returned error code 0xC020200E.

 

Cause

This was because the flat file source connection string was created by an expression. The expression used a parameter and this parameter was empty.

 

Solution

When the parameter was filled with the correct value, eg “C:\Data\Test.csv”, the error was solved.

Handling empty fields during SSIS CSV import

Just a reminder:

If you have a CSV file containing the data:

DataInColumn1Row1,DataInColumn2Row1,DataInColumn3Row1
,DataInColumn2Row2,DataInColumn3Row2
DataInColumn1Row3,DataInColumn2Row3,
DataInColumn1Row4,,DataInColumn3Row4

and you import the columns using a SSIS package the empty columns will be converted to an empty string (“”) and not NULL.

Unit testing SSIS 2008 expressions

In Microsoft SQL Server 2008 Integration Services, you can use expressions, see (Integration Services Expression Concepts, http://technet.microsoft.com/en-us/library/ms141827.aspx) in your dataflow tasks.

For instance, if you want to import a flat file containing 2 columns: FirstName and LastName and your destination table contains only one column: Name, then you can concatenate the two columns by using a SSIS expression: @FirstName + @LastName, this is a very simple example, but when you are creating larger expressions like:

((DATEDIFF("ss",(DT_DBTIMESTAMP)(SUBSTRING(DatumVoormelding,5,2) + "/" + SUBSTRING(DatumVoormelding,7,2) + "/" + SUBSTRING(DatumVoormelding,1,4) + " " + SUBSTRING(TijdstipVoormelding,1,2) + ":" + SUBSTRING(TijdstipVoormelding,3,2) + ":" + SUBSTRING(TijdstipVoormelding,5,2)),(DT_DBTIMESTAMP)(SUBSTRING(DatumSorteerbeslissing,5,2) + "/" + SUBSTRING(DatumSorteerbeslissing,7,2) + "/" + SUBSTRING(DatumSorteerbeslissing,1,4) + " " + SUBSTRING(TijdstipSorteerbeslissing,1,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,3,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,5,2)))) > @[User::MinTijdsduurVoorgemeld]) ? 1 : 0

things can get more complicated and then you might want to use a unit test, to test this specific expression.

There are several ways to unit test, a SSIS expression:

  • Converting the SSIS expression to a SSIS Script Task, some calculation can’t be done with SSIS expressions, then you can use a SSIS Script task, this task can be written in C# and the C# code can be unit tested in Visual Studio.
  • Adding a second SSIS project to your solution, that contains SSIS dataflow task for every unit test

If you just want to manually test the expression without running the complete package, you can create a second SSIS Project to your solution and use flat file source, destination and dataflow tasks to unit test only the SSIS expression. In the screen dumps I added a second SSIS project named: Tpp.Miss.Etl.UnitTest

 

image

UnitTest_Voorgemeld_Input.txt
The UnitTest_Voorgemeld_Input.txt contains all the input data needed to unit test the SSIS expression:
20100521,083030,20100521,084030
20100521,,20100521,084030
20100521,083030,20100521,

 

Data flow task
The data flow task contains:

  • Flat File Source (UnitTest_Voorgemeld_Input)
  • Derived Column (contains the SSIS expression)
  • Flat File Destination (UnitTest_Voorgemeld_Output)

 

image

Flat File Source

image

 

Derived Column (contains the SSIS expression)

Because the Derived column is “Added as a new column”, we must add this new column to the output flat file connection and map the derived column to the output column, to store the expression result in the unit test output file.

image

Flat File Destination

The flat file destination, contains one extra column: Voorgemeld

image

Extra column must be mapped:

image

 

 

UnitTest_Voorgemeld_Output.txt

The UnitTest_Voorgemeld_Output.txt contains all the unit test output, after running the unit test package the result will be:

image

In the file UnitTest_Voorgemeld_Output.txt:
20100521,083030,20100521,084030,20100521083030
20100521,,20100521,084030,20100521
20100521,083030,20100521,,20100521083030

 

By storing the unit test data en the unit test package in the same solution as the SSIS project that uses the expression, our continuous integration server can run the complete packages and the individual unit tests in the nightly builds.

 

 

 

 

 

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 resolve: SSIS package won’t stop debugger on script task

When you are on a x64 machine and de business intellegence studio doesn’t stop the debugger in you’re script task on the break points, set you’re package to x86 (Run64BitRuntime = False) to start debugging the package.

image

Make sure the package is selected en not the individual task (then the debugger also won’t stop).

SSIS 2008 Script Task using and reference a custom none GAC (not signed) assembly

 

If you reference an custom none GAC assembly from you’re SSIS 2008 script task, you can get an exception, stating the assembly could not be found.

Error

Error: 0x1 at Filesysteem opschoning: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘Ada.Cdf, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Ada.Cdf, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’
   at ST_124cffcdeb3f407fa68f54b9192c0cdf.csproj.FileSystemCleaner.Clean()
   at ST_124cffcdeb3f407fa68f54b9192c0cdf.csproj.ScriptMain.Main()

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

   — End of inner exception stack trace —
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Filesysteem opschoning
Warning: 0x80019002 at OpschoonProces: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "OpschoonProces.dtsx" finished: Failure.

 

Solution

To prevent this error, I use the AppDomain AssemblyResolve event to add a folder to the .NET assembly probing (search) path:

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Reflection;
using System.IO;
namespace ST_124cffcdeb3f407fa68f54b9192c0cdf.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
// Add bin folder to .NET assemlby search path
this.BindAssemblyResolveEventHandler();
FileSystemCleaner cleaner = new FileSystemCleaner();
cleaner.Clean();
Dts.TaskResult = (int)ScriptResults.Success;
}
/// <summary>
/// Add an evenhandler for adding a folder to the .NET assemlby search (probing) path.
/// </summary>
public void BindAssemblyResolveEventHandler()
{
var currentDomain = AppDomain.CurrentDomain;
currentDomain.AssemblyResolve += LoadAssemlbyFromProductInstallationFolder;
}
/// <summary>
/// This function is called when the .NET runtime searches for an assemlby to load and can't find that assembly in the current search path.
/// The current search path includes "bin folder application", the global assemlby cache, system32 folder etc.
///
/// This function adds a folder to the current search path at runtime.
///
/// An assembly can be a dll or exe, the ResolveEventArgs argument does not cotain this information.
/// The code will first check if a dll exist in the given folder, if found it loads the dll.
/// If the dll is not found, the code checks if an executable exists in the given folder, if found it loads the exe.
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
/// <returns></returns>
public Assembly LoadAssemlbyFromProductInstallationFolder(object sender, ResolveEventArgs args)
{
Assembly result = null;
if (args != null && !string.IsNullOrEmpty(args.Name))
{
var folderPath = GetExecutionPath();
var assemblyName = args.Name.Split(new string[] { "," }, StringSplitOptions.None)[0];
var assemblyExtension = "dll";
var assemblyPath = Path.Combine(folderPath, string.Format("{0}.{1}", assemblyName, assemblyExtension));
if (!File.Exists(assemblyPath))
{
assemblyExtension = "exe";
assemblyPath = Path.Combine(folderPath, string.Format("{0}.{1}", assemblyName, assemblyExtension));
}
result = Assembly.LoadFrom(assemblyPath);
}
return result;
}
/// <summary>
/// Get the current execution path
/// </summary>
/// <returns></returns>
public string GetExecutionPath()
{
return new Uri(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase)).LocalPath;
}
}
}