SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found


IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found

Author
Message
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50049 Visits: 21151
Debbie Edwards (3/6/2013)
Im horribly close to having to sign this off as a no can do at the moment which is really dissapointing.

From what I can gather I possibly need to execute the VBA Script using c# script within the IS Task but again there seeems to be no documentation on how to do this.

But Again I cant get enough out of the information I have to know that that is right

Sad


You need a Script Task in SSIS which will perform the Excel automation for you. This task would need to effectively load the spreadsheet in Excel - programmatically - and call the macro. That's roughly what the link I posted was doing - albeit from a native C# app rather than a script task (should not be much difference).

Without C# or VB skills, I believe you will struggle to complete this task, I am afraid.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 696
Phil Parkin (3/6/2013)
Debbie Edwards (3/6/2013)
Im horribly close to having to sign this off as a no can do at the moment which is really dissapointing.

From what I can gather I possibly need to execute the VBA Script using c# script within the IS Task but again there seeems to be no documentation on how to do this.

But Again I cant get enough out of the information I have to know that that is right

Sad


You need a Script Task in SSIS which will perform the Excel automation for you. This task would need to effectively load the spreadsheet in Excel - programmatically - and call the macro. That's roughly what the link I posted was doing - albeit from a native C# app rather than a script task (should not be much difference).

Without C# or VB skills, I believe you will struggle to complete this task, I am afraid.


What I was hoping for is something like

This is the extra bit of code you need to add and this is why...

And here is where you put the VB Code you have already created.

At the moment I dont know what section is what, or if I need to change the code I have already created to include more information.

I thought there would be something somewhere that would take you through the process step by step so I could learn how to do it.

Do I need to add C# code or can I stick with VB?


From what you have just said I do have a script task in SSIS
The Macro I have successfully does everything I need it too with the xls
The only bit I have missing is where I call the macro within the task
Thats the only bit Im missing.
And I cant work out where that bit is in the link you sent me an how it works with the VB Script I already have.I do hate so being beaten when I feel like Im so near. Crazy
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2339 Visits: 907
I was under the impression that you can output data to Excel from SSIS but you can't format it; which it sounds like it is what you are trying to do.

The following is only a suggestion and would only work in Excel 2007 and higher.

From office 2007 onwards, all the document types (docx,xlsx etc) are effectively XML files with a different extension; all of the formatting is held within the XML tags. You could use a script to generate the document headers and follow the DOM (available from the microsoft site) to actualy build the XML string that will create the document. It really depends on how complicated your formatting gets.

I admit to never having used this technique in SSIS, but I did successfully write programs in Progress4GL which generated a xlsx document with cell formatting and the principle woudl be similar.

Another alternative would be to have a .bat file that opens Excel with a specific file and macro that loops through the created spreadsheets and applies the formatting. It is a wicked combination of technologies and I wouldn't fancy managing the ongoing development but in one respect it is easier because the components are all separate and the stack can be built gradually.

Excel Master file with a macro that runs on opening
.bat file that calls Excel with the Master File which triggers the script to run
Execute process task that calls the batch file
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 696
Thanks for that. I wouldnt go for the second option, Its way beyond my capabilites.

There are lots of docs on the internet saying you can add a script task in Integratin services and then deploy the vBA script from their (The macro)

bu then it all gets vague and doesnt tell you the specifics on how to do it. Its very frustrating. I have code which works and an IS package to add it too.

I just dont have the bit of script that would call the macro wthin the package.

I have a meeting with my boss tomorrow where Im going to tell him Ive been beaten. Gutted!
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 696
As an extra on this...

I went back to the original error in the Integration Services script package

The Binary Code for the script is not found. Please open the script in the designer by clicking edit script button and make sure it builds successfully

I hadnt realised I could click Build so I did and now I have a new error coming up (Which is at least getting somewhere)

Statement is not valid in a namespace.

Using this I think I might have found a forum post that does what I need. Gives you the extra bit of script and shos you where the code goes

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/87f896aa-5fff-40d3-a054-b0cc4d742ad4

Im not totally sure yet as I havent tested it but that was what I should have done... Clicked build and then used this to get further errors
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 696
Ok so I am further on with it now I have clicked build.

I have added some more script around the code I already had

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task within Integration Services

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Sub FormatSpreadsheetPS_V3()

My VBA Script

End Sub

End Class

Now I have done this I have lots and lots of errors such as......

'DisplayAlerts' is not a member of 'Microsoft.SqlServer.Dts.Runtime.Application'
Name 'Workbooks' is not declared.
Name 'curr' is not declared.
Name 'ActiveWorkbook' is not declared.
Name 'EXH' is not declared.

And so on and so on so obviously I need to do something else. The good thing is that at least its doing something now.

If any one has any best guesses what my next move should be it would be greatly apprieciated.

(I imagine I may get a comment that just says declare everything, I understand that but some more input on how you go about doing this would be good)

Debbie
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14051 Visits: 11848
Did you verify that Excel is installed on the machine you are trying to execute the SSIS package on?
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 696
Yes Im happy that Excel is on the machine and that the right version is being used.

I also went through this documentation

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8b52557b-cd13-4db6-87b6-230b7e051a9e/

http://www.microsoft.com/en-us/download/confirmation.aspx?id=227

http://www.databasejournal.com/features/mssql/article.php/3921141/SSIS-Script-Task-and-Microsoft-Office-Automation.htm

to ensure the correct Excel object is referenced.

o If you are automating Microsoft Office Excel 2007, the type library appears as Microsoft Excel 12.0 Object Library in the References list.
o If you are automating Microsoft Office Excel 2003, the type library appears as Microsoft Excel 11.0 Object Library in the References list.
o If you are automating Microsoft Excel 2002, the type library appears as Microsoft Excel 10.0 Object Library in the References list
o If you are automating Microsoft Excel 2000, the type library appears as Microsoft Excel 9.0 Object Library in the References list.
o If you are automating Microsoft Excel 97, the type library appears as Microsoft Excel 8.0 Object Library in the References list


So Im happy with all that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search