Over the past few days I have been migrating all the DTS package on SQL Server 2000 to SQL Server 2008 64-bit machine. This article will provide insight into how DTS or SSIS packages using excel automation/excel source or destination can be migrated from SQL Server 2000/2005 32-bit environment to 64-bit SQL Server 2008.

Many of you will be aware that Excel doesn't have a 64-bit engine and more often than not we run into numerous issues while migrating from a 32-bit machine to a 64-bit machine.The biggest hurdle that a DBA/Developer faces while debugging is that the COM subsystem doesn't provided us with a helpful error message. When we run into trouble we need as much information as possible to rectify the issue.

So let us get started with migrating a DTS package that has quiet a few Excel components(Destination/source) and an activex script that opens up Excel and saves the data in Excel.

A typical example is given below:

My client requires a feed from our database and the feed should be of the format header,data and footer. The header contains the following information:

The DATA contains all the information required by our client.

Finally,the footer

All the retrieved information is stored in a 3 individual text files.I use an ActiveX script to concatenate the file Header + Data + Footer and create a DAT file.In Visual Studio 2008 I replaced the ActiveX script in the DTS package with a Script Task,which allows a script to be written in C# or VB. I managed to write the code that would convert the DAT file into a CSV and a CSV file to an Excel file by using the "Open" and "SaveAs" function.This is done within a C# script shown below.

/*
 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 Microsoft.Office.Interop.Excel;
//using System.Data.OleDb;
using System.IO;
using System.Collections.Generic;
using System.Runtime.InteropServices;
namespace ST_860f215ab01e4f2196719b52df49af29.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()
 {
 // TODO: Add your code here
 //Dts.TaskResult = (int)ScriptResults.Success;
 // TODO: Add your code here
 //Dts.TaskResult = (int)ScriptResults.Success;
 Microsoft.Office.Interop.Excel.Application excelApp = null;
 Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
 string s_workbookpath = null;
 string s_report_file_name_temp = Dts.Variables["s_report_file_name_temp"].Value.ToString();
 string s_report_file_name = Dts.Variables["s_report_file_name"].Value.ToString();
 try
 {

 string s_filename = Dts.Variables["s_text_file_name"].Value.ToString();
string s_newfilename = s_report_file_name_temp + s_filename.ToUpper();
 string s_sourcefilename;
//read data from header file
 s_sourcefilename = s_report_file_name_temp + "header.txt";
 string text = File.ReadAllText(s_sourcefilename);
 File.WriteAllText(s_newfilename, text);
//read the data file
s_sourcefilename = s_report_file_name_temp + "data.txt";
 text = File.ReadAllText(s_sourcefilename);
 File.AppendAllText(s_newfilename, text);
//read the footer file
s_sourcefilename = s_report_file_name_temp + "footer.txt";
 text = File.ReadAllText(s_sourcefilename);
 File.AppendAllText(s_newfilename, text);

s_newfilename = s_filename.Substring(0, (s_filename.Length - 3)) + "csv";
File.Copy(s_report_file_name_temp + s_filename, s_report_file_name_temp + s_newfilename, true);
 File.Copy(s_report_file_name_temp + s_filename, s_report_file_name + s_newfilename, true);
if (File.Exists(s_report_file_name_temp + s_filename.Substring(0, (s_filename.Length - 3)) + "xls"))
 {
 File.Delete(s_report_file_name_temp + s_filename.Substring(0, (s_filename.Length - 3)) + "xls");
 }

 //open the Excel application
 excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Set the path of the file that needs to be opened
 s_workbookpath = s_report_file_name_temp + s_newfilename;
//Open the CSV file
 excelWorkbook = excelApp.Workbooks.Open(s_workbookpath,
 0,
 false,
 Type.Missing,//leave the formatting as it is.changing this will cause error.
 Type.Missing,
 Type.Missing,
 true,
 Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
 "",
 false,
 false,
 0,
 false,
 false,
 false);
//Set the file name to have xls extension.
 s_workbookpath = s_report_file_name_temp + s_filename.Substring(0, (s_filename.Length - 3)) + "xls";
//Save the file with the "s_workbookpath" name
 excelWorkbook.SaveAs(s_workbookpath,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
 Type.Missing,
 true,
 Type.Missing,
 Type.Missing,
 Type.Missing);
//Copy the saved file to non-temporary location
 File.Copy(s_workbookpath, s_report_file_name + s_filename.Substring(0, (s_filename.Length - 3)) + "xls", true);
string queryString;
 queryString = "exec [PR_SEND_NOC_ESR_FILES] @ATTACHMENTS =";

 queryString = queryString + "'" + s_report_file_name + s_filename.Substring(0, (s_filename.Length - 3)) + "xls;" + s_report_file_name_temp + s_filename + "'"; //+ ";" + s_report_file_name + "CancelledAbsences\\CancelledAbsences.xls'";
 //queryString = queryString + "'" + s_report_file_name_temp + s_filename.Substring(0, (s_filename.Length - 3)) + "csv;" + s_report_file_name_temp + s_filename + "'"; //+ ";" + s_report_file_name + "CancelledAbsences\\CancelledAbsences.xls'";

 Dts.Variables["s_sql_send_noc_esr_files"].Value = queryString;
Dts.TaskResult = (int)ScriptResults.Success;
 }
 catch (Exception e)
 {
 Dts.Events.FireError(0, String.Empty, e.Message, String.Empty, 0);
 Dts.TaskResult = (int)ScriptResults.Failure;
}
 finally
 {
 //Cleanup 
 GC.Collect();
 GC.WaitForPendingFinalizers();
//Close the workbook
 excelWorkbook.Close(true, s_workbookpath, false);
 Marshal.FinalReleaseComObject(excelWorkbook);
 excelWorkbook = null;
 //Quit the application
 excelApp.Quit();
 Marshal.FinalReleaseComObject(excelApp);
 excelApp = null;
}
 }
 }
}

The above code reads the three text files(Header,Data and Footer) concatenates the files into one file with the extension ".DAT".In the next phase the ".DAT" file is renamed to ".CSV".The last phase is to open the file using Excel and save it as an ".xls" file. I saved the SSIS package and while debugging I obtained an error message which stated "Excel is 32-bit and can't be run on a 64-bit machine".

While debugging any SSIS package that uses Excel make sure that the "Run64BitRuntime" setting is always set to false.

One way to do this is Go to the Solution Explorer,right click on the Project and click properties or go to the project Menu click on the Properties option(the last option in the Project Menu) and you will stumble on Property page. Run64BitRuntime needs to be set to false. Refer to the images below.

Once this option was set to false the error message didn't appear. Now when I implemented the SSIS package as a job I had to alter the Execution option to be 32-bit runtime.

The SSIS package worked fine while I was debugging, so why did it fail when I ran it as a job? I have changed the execution mode to 32-bit still it didn't work. Why???

I tried various things to get it working but alas I failed miserably. I found out that the Open and Save As commands in C# were the cause of the problem.

//Open the CSV file
 excelWorkbook = excelApp.Workbooks.Open(s_workbookpath,
 0,
 false,
 Type.Missing,//leave the formatting as it is.changing this will cause error.
 Type.Missing,
 Type.Missing,
 true,
 Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
 "",
 false,
 false,
 0,
 false,
 false,
 false);
//Save the file with the "s_workbookpath" name
 excelWorkbook.SaveAs(s_workbookpath,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
 Type.Missing,
 true,
 Type.Missing,
 Type.Missing,
 Type.Missing);

The solution to this is simple ,in Windows 2008 x64 machine the systemprofile should have a 'Desktop' folder (Windows 2003 had such a folder). I created the folder "C:\Windows\SysWOW64\config\systemprofile\Desktop" and after this the job ran without any problems.

Here is a link that discusses the need for creating the Desktop folder in 64-bit Windows 2008: http://social.msdn.microsoft.com/Forums/en/innovateonoffice/thread/b81a3c4e-62db-488b-af06-44421818ef91

Conclusion

Migrating DTS packages that have Excel Automation or use Excel as the source or destination can be tricky. Some of the steps shown here will help overcome few of the issues that one may face while recreating,testing and implementing the SSIS package on a 64-bit Windows 2008 system.