SSIS Data Import from Excel

I’ve always found the SSIS import tool a bit clunky. I tried to import a CSV file and it get crapping out before completing.

– Copying to [dbo].[export] (Stopped)

Messages

  • ·Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column “Email Address” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
    (SQL Server Import and Export Wizard)
  • ·Error 0xc020902a: Data Flow Task 1: The “output column “Email Address” (10)” failed because truncation occurred, and the truncation row disposition on “output column “Email Address” (10)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    (SQL Server Import and Export Wizard)

I sifted through the data, only a few hundred rows, and I could not see anything wrong with it. I made the columns the right type and increased the column size more than the largest number of characters in the fields. I searched Google and Microsoft Forums trying a handful of solutions and nothing worked.

So what did I do? I had enough and just opened the file from CSV, into Excel, resaved it as an Excel 2007. Re-ran the SSIS import wizard to open an Excel file instead of a CSV, and voila. I’m still baffled though, as to what exactly the problem is.

Send A File Path from the Windows Context Menu to App

Here’s an easy way to pass the file path to a console app. I needed a way to right click on a folder or a file, and send the path to a console app, where the app does its thing with the file(s).

To show you what I mean:

When I right-click on “coolbeans” it runs the following console app, which simply displays the path:

The C# app is pretty straightforward. Basically, once you have the file path, you can apply any operations on the file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace dan_rocks
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.WriteLine(args[0]);
      Console.ReadLine();
    }
  }
}

So there’s nothing crazy going on in the above sample. You basically just have to add a few entries to the Registry.

If you want to pass a folder path when you right-click on it, and select the option in the context menu, create a new entry:

HKEY_LOCAL_MACHINE/Software/Classes/Folder/Shell

If you want to pass in a file, do the following:

HKEY_CLASSES_ROOT\*\shell

Let’s pass in the filepath to note.exe:

Do that and you’ll see the following: