Script Task Help - Renaming Excel Sheet

  • Hello, I am struggling with this script task that will open up an excel file and rename the first sheet and then save it and close it. I have created the script task and have created the following code but I cannot get it to build. I am using Visual Studio 2008 and I have added a reference to the MS Excel 14.0 Library but I'm getting the following build errors:

    The type or namespace name 'SSISScriptTaskEntryPointAttribute' could not be found (are you missing a using directive or an assembly reference?)

    The type or namespace name 'SSISScriptTaskEntryPoint' could not be found(are you missing a using directive or an assembly reference?)

    And here is the code:

    /*

    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.Runtime.InteropServices;

    using Microsoft.SqlServer.Dts.Runtime;

    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

    namespace ST_305e53f071d14e419306294397c38156

    {

    [SSISScriptTaskEntryPoint]

    public class ScriptMain : VSTARTScriptObjectModelBase

    {

    public void Main()

    {

    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

    excelApp.DisplayAlerts = false;

    string workbookPath = @"C:\SWs\test.xlsx";

    Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,

    0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",

    true, false, 0, true, false, false);

    Microsoft.Office.Interop.Excel.Worksheet wksheet = excelWorkbook.Worksheets[1];

    wksheet.Name = "Total Monthly";

    excelWorkbook.Save();

    excelWorkbook.Close();

    Marshal.ReleaseComObject(wksheet);

    excelApp.Quit();

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    }

    }

  • Firstly, Excel and SSIS will put you in a world of pain. Do you really need to use Excel as a source or destination. If you have another option I would seriously consider it.

    Secondly, Are you getting the error in your development environment or when you deploy the package and run in dtsExec.

    Which version of SQL are you on

    Which version of BIDS/SSDT are you using

    Which version of Excel is on your development machince

    Which version of Excel was used to create the file

    Which version of Excel is on your SSIS server

    Is your development machine 32 or 64 bit

    Is your SSIS server 32 or 64 bit

    Is any part of the spreadsheet protected

  • aaron.reese (3/21/2014)


    Firstly, Excel and SSIS will put you in a world of pain. Do you really need to use Excel as a source or destination. If you have another option I would seriously consider it.

    Secondly, Are you getting the error in your development environment or when you deploy the package and run in dtsExec.

    Which version of SQL are you on

    Which version of BIDS/SSDT are you using

    Which version of Excel is on your development machince

    Which version of Excel was used to create the file

    Which version of Excel is on your SSIS server

    Is your development machine 32 or 64 bit

    Is your SSIS server 32 or 64 bit

    Is any part of the spreadsheet protected

    Sadly, yes I know its a pain in the ***. I have no choice... I am pulling data into our SQL Server from an external data source that only sends us excel files. The annoying part is they are constantly changing the damn sheet names and I want to automate this and not have to open the stupid things and change the name on the sheets before I run my packages. Luckily at least the name of the Excel workbooks are the same every time. I have to import about 13 different files.

    To answer your questions:

    1) SQL 2008 R2

    2) Visual Studio 2008

    3) Office 2010

    4) No idea - but the files are .xls

    5) Office 2010

    6) 64 bit

    7) Hmm... I'm assuming my SSIS is 64 bit since I have 64 bit SQL? Excel can only use the 32 bit SSIS though right?

    8) No

    I have not even got to the point of trying to run it from SQL Server... I'm just in BIDS trying to get the damn script to even build. Getting to run I know will be another story. The packages run just fine in SQL right now without the script task that I'm trying to add.

  • Ok, so I gave up with C# ... VB is easier... lol.

    I did this:

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic 2008.

    ' The ScriptMain is the entry point class of the script.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.Office.Interop.Excel

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

    <System.CLSCompliantAttribute(False)> _

    Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    ' 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, Nothing)

    ' 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 Sub Main()

    Dim oMissing As Object = System.Reflection.Missing.Value

    Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()

    Dim xlBook As Microsoft.Office.Interop.Excel.Workbook

    Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

    Dim laPath As String = "path edited"

    xlBook = DirectCast(xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, _

    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _

    oMissing, oMissing, oMissing), Workbook)

    xlSheet = DirectCast(xlBook.Worksheets.Item(1), Worksheet)

    xlSheet.Name = "Sheet1"

    xlBook.Save()

    xl.Application.Workbooks.Close()

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

    And it builds and it works woo hoo!

    However, now I'm running into the problem with running the entire package... my data flow task won't validate because the sheet name isn't what it is looking for cause it hasn't ran the script task yet.

    Is there a way to force it to not validate until after it does the script task? Or is the solution here to put the sheet name into a variable and plug that into the data source instead?

  • Ok, I found the "delay validation" option and set it to TRUE! Whooo hooo... I was able to get it to execute from within BIDS after validating it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply