July 9, 2008 at 5:40 pm
Hi everyone, Newbie here and about to take on a new "opportunity". SSIS
One question I have is that my boss was told by "someone" that SSIS could open a specified Excel file and run the VBA functions. I would guess that if you load the Excel objects (have Excel installed and referenced) that could be done. Has anyone done this?
I just installed SQL2005 and I am getting errors. I need to work on that first.
Thanks all,
\e
July 11, 2008 at 4:02 am
Any ideas would be helpful. Thanks!
\e
July 13, 2008 at 3:51 am
Well I am able to execute an Excel macro from within VB.net. The VB.net project references a C# assembly that I wrote. It is my understanding that if a legal version of Excel (Office) is installed on the system that is running the VB.Net application then this is okay with Microsoft. If this is an issue, please mention it.
Now I need to get SSIS to run the code. I am experiencing a few issues.
1)At this time I am using the Script Component for my VB.net code. Is there another way to run VB.net code in SSIS?
2) Registering the Assembly. The SSIS VB.net code will need to register the Excel assembly simular to the way that it is registered in my VB.net application. Can this be done? In Script Component I tried to add a reference but only a small list of items were displayed. The options to look elsewhere were not the same as in VB.net. I am having a problem getting that assembly registered. Any thoughts?
The Excel assemblies should already be in the GAC and should be displayed as an available reference but they are not. At least where I am looking for them. Please help.
3) Does my c# assembly need to be added to the GAC? I cannot seem to find where I can add a reference in the SSIS VB.Net code.
Thank you all,
Eric
July 13, 2008 at 8:38 am
Well I found a couple of things out.
1) I think Execute Process Task would work for me for the VB.net code. I will look into that
2) At this time I am seriously considerting writing a console application that calls the SSIS packages instead of SSIS calling the VB.net code to run the VBA macros.
The console application will be placed on the SSIS server that also has Excel (Office) installed. A winforms application could launch it or any scheduling package could be used.
I found this piece of code
Dim app As Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package
app = New Microsoft.SqlServer.Dts.Runtime.Application
app.PackagePassword = "password"
pkg = app.LoadPackage("C:\Projects\Sandbox\IS Sandbox\Package9.dtsx", Nothing)
'pkg.PackagePassword = "password"
pkg.Execute()
I am now attempting to find Microsoft.SqlServer.Dts.Runtime assembly. I have SQL2005 installed on my server.
Many more things to learn. I would still like to know how others would solve the problem or to add additional information.
Here are other links I found useful.
Michael Entin's Blog:
Running SSIS package programmatically
http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx
Where is my SQL Server Integration Services package running?
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx
Software:
DTLoggedExec
A tool that mimics the DTExec behaviour, extending the logging functionalities.
DTLoggedExec is a tool that allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging informations of execution status and package runtime data.
http://www.codeplex.com/DTLoggedExec
Article:
Async Lifestyle: Manage Your Tasks With Service Broker
http://technet.microsoft.com/en-us/magazine/cc160787.aspx
Error:
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
http://support.microsoft.com/kb/918760
Reference:
Microsoft.SqlServer.Dts.Runtime Namespace
The Microsoft.SqlServer.Dts.Runtime namespace contains the classes and interfaces to create packages, custom tasks, and other package control flow elements.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx
Eric
July 13, 2008 at 10:39 am
Eric,
You're a good man... you came on board with a question that, obviously, no one else is able to answer and you continue to post your findings. All I can say is "Thanks"! I don't know if I'll ever need to do this but it sure does seem like your on the right track and I appreciate it a lot!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 2:25 am
Hi Eric,
I have only just seen this post and I am not entirely sure that I understand your requirements but:
There is an event associated with the opening of a workbook (Excel spreadsheet). This event might be used to execute VBA code associated with the workbook. There is another event associated with the activation of a worksheet.
You can gain access to these events by selecting Macro-Visual Basic editor from the Tools menu within the Excel spreadsheet.
You can execute a cmd from within SSIS that opens an Excel spreadsheet.
Hope this helps
regards
Shaun
Quis custodiet ipsos custodes.
July 14, 2008 at 5:13 am
Eric,
What is it that you intend to do with the VBA?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 5:37 am
Thanks for the input.
Requirements:
We need to gather data from two Oracle databse, two SQL 2005 database, and about eight Excel workbooks into a single SQL database. This process will be scheduled to run each evening.
Many of the Excel Workbooks have macros that consolodate the data from other workbooks as well as process the data. Some of these macros use certain third party controls that are only designed for Excel and at this time can only be run in Excel.
We are attempting to build an automated processes that handles this with no (or minimum) user interaction.
Process:
Launch the Excel macros in each wookbook that needs them.
Use SSIS to then pull the data from all data sources and place them in the final SQL database.
Design:
At this time I have a regular VB.net (not in SSIS) code that opens and runs an Excel macro in a workbook and then saves the workbook. This has been tested on a very simple macro and still needs more testing.
I am considering using that same VB code to externally launch the multiple SSIS packages. The problem I had was with finding and referencing the SSIS dll(s). I think it is because I was working in VS2003 (framewoork 1.1) and am currently installing VS2005. Framework 2.0 was installed but I expect VS2003 cannot use the SSIS dll's. If this fixes the problem then I will consider this as well.
As most know if I am going to do this then my code for the Excel macros needs to be run on a system that has Office (or minimum Excel) installed. Also, the code that executes the SSIS packages must be located on a system that can run those packages (i.e. has the dll's that are needed to access the SSIS information).
I am thinking about building a (or possibly two) .net console application(s) that will reside either on the server where the SSIS packages are run and / or on a system that has Excel installed. The best option for me would be to install Office (Excel) on the SSIS server. But I am getting pushback on that from the network group.
A third option would be to skip SSIS altogether. I have the code that can open and either pull or push data (as well as formatting) in Excel. SSIS might allow a simpler solution but also adds an additional application to the mix. So who knows a this point.
It would be really helpful if someone knew (and had code examples) if an Excel macro could be launched from within SSIS. That would probably save time and effort. Using the "Add Reference" in SSIS VS2005 brings up a different (less options) screen and limits me on what assemblies I can register. Maybe that will cahnge once I install VS2005 (with all the other options)
I am still new to SSIS (never heard of it prior to last Tuesday) and have more work to do. I will keep this updated.
Eric
July 14, 2008 at 6:18 am
I'm no DTS or SSIS "Ninja", so I can't help there. What I have done is create another spreadsheet that would open the others and export their data to a TAB delimited file and then import that data. It's not the great optimal solution you're looking for, but it might be a start.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2008 at 8:05 am
I'm not saying that manipulating an Excel file isn't possible from SSIS, but I think it may require some pretty nifty programming to accomplish.
Seems to me that you already have some pieces in place to handle the Excel files. SSIS would be a good ETL solution for the Oracle and SQL extracts. I too would probably stay away from instantiating Excel objects on the SQL server; it's really not a server app and I have read that it's generally not a good idea. Keep the Excel on a workstation.
Perhaps you really just need to centralize the control of what happens and when. This could be done from either the client or server. On the client you could use sp_start_job to call SQL Agent jobs that would execute your SSIS packages and use your existing VB app to handle the Excel. From the server you could use SQL Jobs to run SSIS and use PSEXEC to open the Excel files on the remote workstation.
Either solution has more parts than one would like, but I have had success using either method with few glitches.
July 14, 2008 at 8:32 am
Eric, do you have only one macro per workbook that you need to run? It has been a while (office... um 95? Maybe?) , but you used to be able to launch a macro in excel (or word, powerpoint, access...) by opening a file with a "...\Excel.exe "FileName" /m "MacroName". You could create a macro to execute all needed macros and then exit the excel application. Similarly, you could create a macro that runs on start up, runs all of the needed macros, and then exports a CSV of the results to be imported.
If I were you I would stick with whatever environment you are most comfortable, .Net, VBA, CMD, whatever, format the data as much as possible, and then perhaps control the flow from SSIS with a final import.
Just my thoughts, though it has been a while since I have had to work with any of these.
July 14, 2008 at 10:08 am
Thanks all for your input. This is a learning experience for me.
Shaun, I will look into your thought of event, etc. Have you seen any code / SSIS packagesthat open an excel workbook? Even something simple might get me going in the right direction.
MentalWhiteNoise, Never thought of the /m option. That might work. I will test and report back.
Still Unknowns
Does an assembly that I create need to be added to the GAC before it can be seen as a reference in the SSIS VB Script control? The reference box is different if accessed from VS2005 SSIS and from VS2005 Winforms or Web development.
Can I in some way reference other assemblies by not placing them in the GAC?
Updates to follow...
Eric
July 15, 2008 at 2:26 am
Hi Eric,
The Workbook (spreadsheet) Open event occurs when the workbook (spreadsheet) is opened. So all you need to do is open the Excel file. If Excel is installed on the server simply enter the fully qualified path name e.g. c:\somefolder\somespreadsheet.xls.
Ensure that the security level on the file is set correctly. This is set in the tools menu option under Macros - Security.
If Excel is not installed on the server you will need to contrive some means of opening the spreadsheet on another box where Excel is installed. You are obviously familiar with programming so some sort of remote procedure call should do the trick. .Net remoting is a way of achieving this.
The sample code below was lifted from the the Excel Help files. Just paste it into the WorkBook Open event of a test workbook and save it. Open a cmd window, type in the fully qualified path and hit return. The workbook will open and present you with a Mesaage box. It does work I tried it before submitting it.
[font="Courier New"]
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
[/font]
If you need anything else just post and I'll do my limited best
Good luck
regards
Shaun
Quis custodiet ipsos custodes.
June 10, 2009 at 11:35 pm
hi, was there ever a solution to running a macro from SSIS??
or opening an excel file from SSIS?
I am looking at writing all my macros in an existing excel workbook and placing the main macro in Workbook_Open() so all I need for SSIS to do is launch the file.
June 12, 2009 at 8:14 am
Possibly an alternate approach (that has worked well for me):
Control everything from within one of the Excel Workbooks. Instead of pushing the data from SSIS, define the data in Excel as linked external data source(s) (perhaps that refresh on workbook open), referencing views or stored procedures to pull data from all sources. (Or perhaps from a single SQL Server that stages or accesses data from the multiple heterogeneous linked servers.) Nothing to schedule or coordinate; the data is allways current for the user.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply