March 21, 2014 at 7:25 am
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;
}
}
}
March 21, 2014 at 10:30 am
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
March 21, 2014 at 10:56 am
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.
March 21, 2014 at 12:29 pm
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?
March 21, 2014 at 12:51 pm
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