Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script Task Help - Renaming Excel Sheet Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
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;
}
}
}

Post #1553479
Posted Friday, March 21, 2014 10:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 441, Visits: 670
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

Post #1553600
Posted Friday, March 21, 2014 10:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
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.
Post #1553614
Posted Friday, March 21, 2014 12:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
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?
Post #1553633
Posted Friday, March 21, 2014 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
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.
Post #1553638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse