Here is a 1-2-3 for building your own VB.NET 2.0 Console application that will allow the upload of SSIS packages to SQL Server storage in the MSDB folder of your choice. The process also allows correct operation of XML Configurations on the packages (other configurations should work too, not tested by me in production though).

Here goes!

1. Launch VS.2005. Start a new VB Windows Console Application project.
2. Add Microsoft.SQLServer.ManagedDTS library to References
3. Paste this into Module 1.vb

   Module Module1
   
       Sub Main()
           Dim packages As System.Collections.Specialized.StringCollection = My.Settings.packageManifest
           Dim pkgName As String
           Dim fileName As String
           Dim processContinue As Boolean = True
           For Each pkgName In packages
               Dim pkgFilePath As String = My.Application.Info.DirectoryPath + "" + pkgName + ".dtsx"
               If Not System.IO.File.Exists(pkgFilePath) Then
                   Console.WriteLine("Package " + pkgName + ".dtsx is not found on the local file folder.")
                   processContinue = False
               End If
           Next
           For Each fileName In System.IO.Directory.GetFiles(My.Application.Info.DirectoryPath, "*.dtsx")
               pkgName = System.IO.Path.GetFileNameWithoutExtension(fileName)
               If Not packages.Contains(pkgName) Then
                   Console.WriteLine("File " + pkgName + ".dtsx is found on the local file folder but is not included in the .config  section.")
                   processContinue = False
               End If
           Next
           If Not processContinue Then
               Console.WriteLine("Please verify that all files in the .config  section are on the local folder.")
               Console.WriteLine("Please verify that all files in the local folder are listed in the .config  section.")
           Else
               For Each pkgName In packages
                   PackageHelper.SaveToSQL(pkgName)
               Next
           End If
           Console.Write("Press Any Key ...")
           Console.Read()
       End Sub
   
   End Module
   

4. Create a new class PackageHelper.vb and paste this code

   Imports Microsoft.SqlServer
   
   Public Class PackageHelper
   
       Private Shared Function loadSSISPackage(ByVal pkgName As String) As Dts.Runtime.Package
           Dim dtsapp As Dts.Runtime.Application = New Dts.Runtime.Application
   
           Dim pkg As Dts.Runtime.Package = dtsapp.LoadPackage(pkgName, Nothing)
   
           Return pkg
   
       End Function
   
   
   
       Public Shared Sub SaveToSQL(ByVal pkgName As String)
           Dim dtsapp As Dts.Runtime.Application = New Dts.Runtime.Application
           Dim pkgFilePath As String = My.Application.Info.DirectoryPath + "" + pkgName + ".dtsx"
           Dim ssisFolder As String = "\" + My.Settings.targetSSISMSDBfolderName
           Dim ssisServer As String = My.Settings.targetSSISserver
           Dim ssisPackagePath As String = ssisFolder + "" + pkgName
   
           If Not dtsapp.FolderExistsOnSqlServer(ssisFolder, ssisServer, Nothing, Nothing) Then
               Console.WriteLine("Server:" + ssisServer + "  Folder:" + ssisFolder + "  SSIS Msdb Folder is not found on server.  Check Project Settings (Settings.settings).")
           ElseIf Not System.IO.File.Exists(pkgFilePath) Then
               Console.WriteLine("Package: " + pkgFilePath + "  The file specified is not found in the " + My.Application.Info.ProductName + " current directory.")
           Else
               Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Starting ...")
   
               Dim pkg As Dts.Runtime.Package = PackageHelper.loadSSISPackage(pkgFilePath)
   
   
               If dtsapp.ExistsOnSqlServer(ssisPackagePath, ssisServer, Nothing, Nothing) Then
                   Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Removing Previous Instance ...")
   
                   dtsapp.RemoveFromSqlServer(ssisPackagePath, ssisServer, Nothing, Nothing)
   
               End If
               Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " ...")
   
               dtsapp.SaveToSqlServerAs(pkg, Nothing, ssisPackagePath, ssisServer, Nothing, Nothing)
   
               Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Complete")
           End If
       End Sub
   End Class
   

5. Add Settings.Settings to the project to create the XML configuration file for the installer.Name: targetSSISserver
   Type: String
   Value: your server nameName: targetSSISMSDBfolderName
   Type: String
   Value: Your MSDB Folder Name – This must pre-exist on the target SSIS ServerName: packageManifest
   Type: System.Collections.Specialized.StringCollection
   Value: newline-delimited list of package names. This will be an element list in the XML Config file.An example resulting app.config :

   
   
       
           
               
           
       
       
           
               
                   CHMW003732
               
               
                   Deployment Test
               
               
                   
                       
                           stageGAAPUploadReserves
                           packageNotFound
                       
                   
               
           
       
   
   

6. Build the console app. Copy the SSISPackageUploader.exe and SSISPackageUploader.exe.config to a new folder along with the packages you intend to upload.
7. Modify the SSISPackageUploader.exe.config to contain the intended targetSSISserver, targetSSISMSDBfolderName, and packageManifest

I’ll be blogging some on SSIS as I now have a new gig slinging data hither and yon. Duck, you don’t want to get a DT_I8 in the face.

So, today I was bitten by the IsBig flag on DataFlow Aggregate Transformation. This flag is notated in the BOL:

A column may contain numeric values that require special consideration because of their large value or precision requirements. The Aggregation transformation includes the IsBig property, which you can set on output columns to invoke special handling of big or high-precision numbers. If a column value may exceed 4 billion or a precision beyond a float data type is required, IsBig should be set to 1.

So, the result here is a column that is Input to the Aggregate Transformation as DT_I4 and has IsBig = 1 WILL be Output DT_I8, and the IDE will not allow the otuput datatype to be changed with Advanced Editor!

This bit me with one column in a large Aggregate somehow getting set IsBig and messing up the downstream metadata. Several different looks at the issue were all proving dead ends.

Next time you run into a metadata mismatch, especially if its a DT_I4 or DT_R4 turning into their 8-byte counterparts, remember, you need to tap your inner IsBig and see the light!

I will outline how to setup NUnit testing with WinFX 1.0 (Feb CTP) in order to UnitTest an Avalon Window.

What’s wrong

Points of technology that drive the details.

• WPF windows must run on an STA Thread (Single-Apartment) MS Forums
• NUnit (and TestDriven.net, using the NUnit API) run all tests with an MTA Thread. NUnit Developers Wiki: ExceptionsOnThreads
• In order to generate and start an STA thread from within a running MTA, grasshopper, you must warm up your System.Threading fu and investigate Thread() and Thread.SetApartmentState. GeekNoise : Peter Provost

The recipe to success

1. Create a new class in your test Assembly to hold the NUnitHelper namespace and class.

   using System;
   using System.Collections.Generic;
   using System.Reflection;
   using System.Security.Permissions;
   using System.Threading;
   using System.Text;
   
   namespace NUnitHelpers
   {
   class CrossThreadTestRunner
   {
   private ThreadStart userDelegate;
   private Exception lastException;
   
   public void RunSTA(ThreadStart userdelegate)
   {
   this.userDelegate = userdelegate;
   Thread t = new Thread(new ThreadStart(this.MultiThreadedWorker));
   t.SetApartmentState(ApartmentState.STA);
   t.Start();
   t.Join();
   
   if (lastException != null)
   ThrowExceptionPreservingStack(lastException);
   
   }
   
   public void Run(ThreadStart userdelegate)
   {
   this.userDelegate = userdelegate;
   Thread t = new Thread(new ThreadStart(this.MultiThreadedWorker));
   t.Start();
   t.Join();
   
   if (lastException != null)
   ThrowExceptionPreservingStack(lastException);
   
   }
   
   [ReflectionPermission(SecurityAction.Demand)]
   private void ThrowExceptionPreservingStack(Exception e)
   {
   FieldInfo remoteStackTraceString = typeof(Exception).GetField("_remoteStackTraceString",
   BindingFlags.Instance | BindingFlags.NonPublic);
   remoteStackTraceString.SetValue(e, e.StackTrace + Environment.NewLine);
   throw e;
   }
   
   private void MultiThreadedWorker()
   {
   try
   {
   userDelegate.Invoke();
   }
   catch (Exception e)
   {
   lastException = e;
   }
   }
   
   }
   }
   

   This is adapted from Peter Provost’s original code. First, I compressed his constructor/method calls into just the method call. This allows me the cleanest calling-code that I can get with minimal effort. Second, I duplicated Run() into RunSTA() in order to test STA threads that are executing the WPF Window.

2. Now my calling class (the actual NUnit testing class) can use this new helper to correctly process WPF testing (handling Assert exceptions instead of raising UnhandledException error inside NUnit’s execution path.) :

   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Threading;
   using System.Windows;
   using System.Windows.Automation;
   using System.Windows.Interop;
   using NUnit.Framework;
   
   namespace NunitTest
   {
   [TestFixture]
   public class Class1
   {
   NUnitHelpers.CrossThreadTestRunner runner = new NUnitHelpers.CrossThreadTestRunner();
   
   [Test]
   public void truthtest()
   {
   Assert.IsTrue(true);
   }
   
   [Test]
   public void falsetest()
   {
   Assert.IsTrue(false);
   }
   
   public static void TestAddressbookWorker()
   {
   Window ab = new AddressBookLib.Window1();
   ab.Show();
   ab.Close();
   ab = null;
   Assert.IsTrue(false, "Forced NUNit Failure");
   
   }
   
   [Test]
   public void TestAddressBook()
   {
   runner.RunSTA(new ThreadStart(TestAddressbookWorker));
   }
   
   }
   }
   

   Here each test method requires a worker that contains the actual testing code. The calling test is simply delegating the worker method into the CrossThreadTestRunner instance. Using RunSTA() starts an STA thread and then the WPF window is started happily.

3. NUnit Output!! (For those unbelieving)

------ Test started: Assembly: NunitTest.dll ------

TestCase 'NunitTest.Class1.falsetest' failed:
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestClass1.cs(26,0): at NunitTest.Class1.falsetest()

TestCase 'NunitTest.Class1.TestAddressBook' failed: Forced NUNit Failure
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestClass1.cs(35,0): at NunitTest.Class1.TestAddressbookWorker()
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestCrossThreadTestRunner.cs(55,0): at NUnitHelpers.CrossThreadTestRunner.MultiThreadedWorker()
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestCrossThreadTestRunner.cs(48,0): at NUnitHelpers.CrossThreadTestRunner.ThrowExceptionPreservingStack(Exception e)
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestCrossThreadTestRunner.cs(24,0): at NUnitHelpers.CrossThreadTestRunner.RunSTA(ThreadStart userdelegate)
C:Visual Studio 2005ProjectsWinFXAddressBookNunitTestClass1.cs(42,0): at NunitTest.Class1.TestAddressBook()

1 passed, 2 failed, 0 skipped, took 21.25 seconds.

Ok, it’s been a while… a loong loong while.
All my projects are now in process of being converted to VS.NET 2.0. Currently using the Sept.05 CTP.
Now that a couple of the projects are deployed (using the Beta 2 go-live license, of course… only build on Beta 2… CTP’s are not licenced!) I’m going into TDD and Mocking mode. Looks like RhinoMock gets the win as currently having a build on NetFx 2.0. I’m in the less-work-win-bid category right now… all projects are officially behind due to rewrite.

So, my next post will outline a simple way to test a home-grown data-access web service.

Hello Hello Hello,

It’s been a long time. I’ve had my head buried under several layers of black, gooey, .NET C# UI routines.

Testing UI is NOT a picnic.

I’ve found problems in two categories thus far :

1. Old VB6 tactics result in cross-updating/cross-merging of datasets. Basically MAJOR data collisions. The most common instances of this are a pair of cascading comboboxes where combobox-child has list-data one minute… and no list data the next! The hardest debugging is watching the Step-into pass over a line of code and the dataset has changed… what happened to my internals?!

2. Mocking for UI is scarily complicated. Again here is a bunch of dataset collsions, but also the need to mock the results of data processes (especially updates) for the purpose of the UI. I will note here that coding ADO.NET one-row inserts and updates with specified primary-key values is still 1000% easier than ADO!

OK, If anyone is reading my blather, feel free to comment, suggest.

I especially want to hear of your own experiences or links to other Test-Driven UI efforts.

I will be documenting some of my adventures with Visual Studio .NET, both WinForms and ASP.NET. My main platform is Win2000/XP, SQL Server 2000, IIS 5. We do not perform “disconnected” application programming at my company. External intra-net access is achieved via Terminal Services.