Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2008,Windows 2008 x64 and Excel

By Blesson John,

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.

Total article views: 7087 | Views in the last 30 days: 2
 
Related Articles
FORUM

Running SSRS report from SSIS package

How can we run an SSRS report and export it to Excel from SSIS package

FORUM

Error in exporting report to excel format

Error in exporting report to excel format

FORUM

Strange issue while loading data from Excel using SSIS package

Strange issue while loading data from Excel using SSIS package

ARTICLE

Generate SSRS reports from a SSIS Package

Learn how to invoke SSRS reports from an SSIS package after the data load is completed.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones