SSIS Export to Excel 2007

Thanks to this link: MSDN SSIS Team Blog

The Excel 2007 default output format for the SSIS Excel Destination is Excel Binary Format (.xlsb). If you’re creating a new Excel file, and have given it an .xlsx extension, you’ll get the following error when trying to open it in Excel:

Excel cannot open the file ‘xxx.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

If you want the Excel Destination to output a standard .xlsx file (Excel XML Format), you’ll need to tweak your Excel Connection Manager’s connection string property.

By default it will look something like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsb;Extended Properties="Excel 12.0;HDR=YES";

Changing “Excel 12.0” to “Excel 12.0 Xml” will tell the provider to output in .xslx format instead.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES

Asynchronous processing in Windows forms applications

I recently wrote an application that required handling long running backend processing with progress updates returned to the Windows form. The obvious way to handle this is with multiple threads. But there is a problem with calling an asynchronous process on a separate thread than the Windows form. A call to update a Windows form control from a separate thread (known as cross threading) will cause the application to crash. So after struggling for a while to come up with a solution, I found this article on MSDN: http://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker.aspx. The solution is the System.ComponentModel.BackgroundWorker class. It worked very well for my application.

Web Site Deployed with SilverLight

Over the weekend, I deployed the new web site for ACE NET Consulting, an IT consulting and professional services company located in Tulsa. The site features some great new technology that I'm really proud of. 

The first feature to point out is the cover flow image scroller on the home page. It is a SilverLight control that pages through ACE NET's products and services. We wrote the entire thing from scratch, including the 3D transformations and effects that make the images appear to float in and out of perspective. The control uses an XML file to dynamically load the images at start up and set the title and hyperlink to navigate to when the user clicks on the center most image. The second feature we are very happy with is the platform we used to manage the site content. The site is controlled completely by a content management system which allows for easy content update directly on the site. No external editing tools and no HTML coding required!

First iPhone App Released

Vision Database AcuXDBC ODBC integration with Microsoft SQL Server

Every once in a while you come across a problem that you wish someone else had faced before and posted the solution on the web. Just yesterday I found myself in that situation. So for anyone else out there that may encounter this same problem, here is my solution. My client has a Vision database for their Passport accounting system. They needed a way to pull data from their Vision database into their Microsoft SQL Server 2005 database (Express edition). The way I handled this was through the Acucorp extend AcuXDBC ODBC driver. The challenge was getting SQL to work correctly with the AcuXDBC ODBC driver. I first created a OLEDB for ODBC linked server inside of my SQL instance. Don't try this. The AcuXDBC driver doesn't like that. The only way I got it to work was by using the SQL command OpenRowSet(). I'll explain how this command works later, but first some additional gotchas:

I installed a new instance of SQL Express 2005 but did not reboot. I think this is why I kept getting the Vision ODBC error "GENESIS_HOME" environment variable not set - even though it was set. I noticed that that "GENESIS_HOME" environment variable was only set for the Administrator account and not system wide. So be sure to check for this and fix it before you do the reboot. In order to run OpenRowSet() commands you have to enable "Ad Hoc Distributed queries". Here is how to do it:

sp_configure ’show advanced options’, 1 reconfigure GO
sp_configure ‘Ad Hoc Distributed Queries’, 1 reconfigure GO

Now you can use the OpenRowSet() command. Here are the steps to do this:

Setup the AcuXDBC odbc driver as a System DSN. I named mine "PBS". Follow the AcuXDBC documentation for configuring the the ODBC driver. In my case it was already done for me. Here is how to use the OpenRowSet command. Note that the query syntax has to be SQL-92 compliant.

SELECT a.* from OPENROWSET('MSDASQL', 'DSN=PBS', 'select * from "mylibrary"."mytable"') as a

Good luck to you if you encounter this problem. Hopefully it won't take you 8 hours to get it working like it did for me.

Brainbench certification in C#

I'm currently working towards the Microsoft Certified Professional Developer (MCPD) certification. After weeks of self paced studying, I figured it was time to take a test. I figured a good place to get an exam was Brainbench.com and I was right. Several of the questions covered in the exam were not in the Microsoft literature that I have been studying. So this gave me a chance to prove my knowledge. I took the C# exam. It is 40 questions long and covered the following topics:

Storing files in a database - Part 2

This is part 2 of a 2 part article. In Part 1 I explained how to insert a file image into a SQL database using Microsoft SQL Server and the Microsoft .NET Framework. In this article I explain how to retrieve the file image from the database and display it to the application user. One way to do this is to use ASP .NET as a front end layer. This is probably the easiest way to handle the task. For this example we have a ASP .NET web page called "Open.aspx". Open.aspx and Open.aspx.cs Recall from part 1, that the database table where the file images is stored contains a unique identifier (a.k.a a GUID) field. This is the value the Open.aspx page will use to request the file from the database. In the code behind file, Open.aspx.cs there is a page load event as shown below.

protected void Page_Load(object sender, EventArgs e)
{
  try
  {
    string strFileId = Request.QueryString["FileId"].ToString();
    if(!string.IsNullOrEmpty(strFileId))
    {
    LoadAttachment(strFileId);
    }
  }
  catch
  {
    //Do nothing
  }
}

When the page loads it reads in the query string value "FileId". Once the FileId is known the page then calls the local method "LoadAttachment". The LoadAttachment method makes a call to our custom class which handles database operations. Below is the code. Method LoadAttachment()

private void LoadAttachment(string FileId)
{
  try
  {
   Attachment MyAttachment = null;
   MyAttachment = Attachment.GetOne(new Guid(FileId));

   //The real magic starts here:
   //Transform page from HTML into the content of the file
   Response.Clear();
   Response.ClearContent();
   Response.BufferOutput = true;
   Response.AddHeader("Content-Disposition", "attachment;
     filename=" + MyAttachment.FileName);
   Response.ContentType = "application/octet-stream";

   //Now write the contents of the file into the page
   Response.OutputStream.Write(MyAttachment.FileImage, 0
     MyAttachment.FileImage.Length);

   //Close the Response stream
   Response.Flush();
   Response.Close();

   //At this point the File Download dialog will appear.
  }
  catch(Exception ex)
  {
   response.Write("Error: " + ex.Message);
  }
}

The LoadAttachment method causes the File Download dialog box to appear. 

 The last little bit of code to share is the custom Attachment class method GetOne. The GetOne method is called by the Open.aspx.cs LoadAttachment method. Static method: Attachment.GetOne()

public static Attachment GetOne(Guid FileId)
{
  Attachment MyAttachment = null;

  SqlConnection sqlCon = new SqlConnection(ConnectionString);
  sqlCmd = new SqlCommand(@"
  select file_id, file_name, file_image
  from   Attachments
  where  file_id=@file_id", sqlCon);
  sqlCmd.CommandType = CommandType.Text;
  sqlCmd.Parameters.Add("@file_id",
    SqlDbType.UniqueIdentifier).Value = FileId;
  sqlCon.Open();
  SqlDataReader Reader = sqlCmd.ExecuteReader();
  if (Reader.Read())
  {
    MyAttachment.FileId = new Guid(Reader["file_id"].ToString());
    MyAttachment.FileName = Reader["file_name"].ToString();
    MyAttachment.FileImage = (byte[])Reader["file_image"];
  }
  Reader.Close();

  return MyAttachment;
}

Note that when the SQL Data Reader accesses the file_image field it is required to cast the content into a byte[] array. That's all that's needed to read a file from the database and display it. Please note that the code samples shown have been simplified for demonstration purposes. You'll need to be sure to add proper error handling if you choose to sample the code found in this article.

Storing files in a database - Part 1

With Microsoft SQL Server and the Microsoft .NET Framework it's relatively easy to store files in a database. This is something that I've done on several occasions. Until recently I hadn't taken the time to write a formal class library that could be re-used with relative ease. I'm going to share a few code snippets from that class library that will provide enough information to see how it's done. A quick note about the project environment: The code samples are written in C# .NET 2.0. The database is SQL 2000 - although I'm sure that SQL 2005 would work seamlessly with the code samples The Attachment SQL table The SQL table where the files are stored is named "Attachments" and has the following fields: 

 The Attachment class library The Attachment class library is what I wrote to handle the requirements of the last project I completed that required database file storage. It does most of the work for us so we don't have to spend any time in the user interface layer defining how the file gets uploaded to the database. Among others it has the following properties and methods:

public Guid FileId;
public string FileName;
public byte[] FileImage;
public DateTime AttachedDate;
public void Create();

The "FileImage" property is the field that stores the byte stream of the file to be uploaded. The contents of the property will be written to the database. So all we need to do is instantiate the Attachment class and find a way to populate the class's required properties and call the Create() method. We will use the ASP .NET File Upload control to perform this operation. Uploading files to the database using the ASP .NET File Upload control 

 An easy way to provide an interface to upload files to a database is to use the ASP .NET File Upload control. The File Upload control is a control that was introduced in ASP .NET 2.0. Our file upload control is shown below. When the user clicks on the "Upload" button the following code fires.

private void AttachFile()
{
  if (FileUpload1.HasFile)
  {
    //Instantiate our Attachment class.
    Attachment MyAttachment = new Attachment();

    //Set Attachment properties.
    MyAttachment.FileName = FileUpload1.FileName;
    MyAttachment.AttachedDate = DateTime.Now;
    byte[] FileBytes = FileUpload1.FileBytes;
    MyAttachment.FileImage = FileBytes;
    MyAttachment.Create();
  }
}

That's all that has to be done from the ASP .NET user interface. Notice that the File Upload control provides a property named "FileBytes". The FileBytes property holds the byte stream of the file to be uploaded. All we have to do is set the Attachment class property "FileImage" to the value of the FileUpload1.FileBytes property. The real work is done in the Attachment class Create() method. Attachment class Create() method The Create method handles inserting the file into the database. It's very straight forward. The code sample is below. Notice the SQL command parameter @file_image is used to insert the contents of the Attachment.FileImage property into the database.

public void Create()
{
  SqlConnection sqlCon = new SqlConnection(ConnectionString);
  try
  {
    string sqlQuery = @"
    insert into Attachments(
    ,file_id
    ,file_name
    ,file_image
    ,attached_date)
    values (
    ,@file_id
    ,@file_name
    ,@file_image
    ,@attached_date)";

    sqlCmd = new SqlCommand(sqlQuery, sqlCon);
    sqlCmd.CommandType = CommandType.Text;
    Guid NewFileId = new Guid();
    sqlCmd.Parameters.Add("@file_id",
    SqlDbType.UniqueIdentifier).Value = NewFileId;
    sqlCmd.Parameters.Add("@file_name",
     SqlDbType.NVarChar, 256).Value = this.FileName);
    sqlCmd.Parameters.Add("@file_image",
     SqlDbType.Image).SqlValue = new SqlBinary this.FileImage);
    sqlCmd.Parameters.Add("@attached_date",
     SqlDbType.DateTime).Value = this.AttachedDate);
    sqlCon.Open();
    sqlCmd.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
    throw ex;
  }
  finally
  {
    sqlCmd.Dispose();
    sqlCon.Dispose();
  }
}

That's all for part 1 of this posting. Please note that the code samples provided have been simplified for this example. They will work as intended, but if you plan to use this code in a production environment you'll want to be sure to add proper error handling to your routines.