?
Solved

How do I take an excel vba solution and place it in an SSIS package so that it can be scheduled to run on it's own

Posted on 2011-10-27
23
Medium Priority
?
395 Views
Last Modified: 2013-11-10
Hi Experts,

I want to know how to take the vba solution from this previous post and place it in an SSIS package that can be scheduled to run so that I won't have to open the excel file and push the buttons to get the results.  Is this possible within SSIS?  Any help would be greatly appreciated!!
0
Comment
Question by:daintysally
  • 8
  • 7
  • 4
  • +1
20 Comments
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37037944
I'm not sure what you have going for a VBA solution from a previous post but it is possible to process Excel files in a SSIS package.

For processing Excel files in SSIS, if an Excel spreadsheet is the datasource, you need to change the package properties, making sure that the property, Run64BitRuntime is set to false (assuming that you're developing on a 64 bit machine).

If you're creating an Excel file in a SSIS package, the file must already exist--create a template (e.g. a blank spreadsheet).

Also, for scheduling a job and running a SSIS package, you need to make sure you create a proxy account on the SQL Server where the package resides.

Go to pragmaticworks.com and you'll find a bunch of webinars on SSIS.  I've been working with SQL Server for 10 years and there is a learning curve using SSIS.  Between the webinars, reading the SSIS book by Brian Knight, attending the 2 day workshop about SSIS (by Brian Knight), and googling the various things I needed to do, I've gotten a better handle on it.
0
 

Author Comment

by:daintysally
ID: 37038157
Hi mgfagan,

I have attached the excel vba solution from the previous post.  What I want to do is take this solution and put it inside of an ssis package so that it can run from a schedule instead of me going into this file everyday to run it; or is it possible to recreate this solution inside of ssis?
inAnotB-r5.xls
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37038365
You are not going to be able to take the solution as is and put it in a SSIS package and run it.  In all of my research on SSIS, I've never seen the question or seen an example of running macros from an Excel spreadsheet within a package.  Excel spreadsheets have been used as data sources or data destinations.  

I did not look at the macros and such--I haven't done VBA programming in years and only did it in Access when I did so I'm not sure what all this spreadsheet is doing--is it pulling in data from a SQL Server database?  Modifying data within itself?  If it's the former, it would be possible to create a SSIS package for it.  If it's the latter, I believe you would probably have to break it up and have the Excel spreadsheet as the source, use your VBA code as guide for the script code that you'd have to write (which would be in VB.NET or C#), and then have a template for the destination.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:daintysally
ID: 37038511
This spreadsheet compares columns A & B by using the reference table in columns J & K.  If the names in column A & B do not match, then the cell is it is highlighted red; all of this code is behind the 'Compare Show Diff's' button.

The spreadsheet is not pulling the data from a sql server database, but it needs to in order to get the column names of the table (it was pasted in for example purposes).  What I need to be able to do is pull the column names from a sql server table and then pull the column names from the first row of an excel file and compare them .  If there are differences, then I need to know.  

I have done exactly what you have suggested so far.  I have created an SSIS package and I have pulled in the excel spreadsheet as the source on the control flow.  Now I know that I have to add a script component, but I am struggling with the script (VB.net and C#) that needs to be written.  This is what I am having a problem with as I am not very strong in either.
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37038803
Does this have to be in a spreadsheet for output?  If not, I'd import the data into a SQL Server table and do a comparison (via stored procedure) and e-mail the results.  That would eliminate the need for writing script code.  You'd have a SSIS package that imports the spreadsheet into a SQL table, then call a stored procedure (that you'd write) that would query against the reference table and e-mail the results.
0
 

Author Comment

by:daintysally
ID: 37038895
No, it does not need to be in a spreadsheet for output.  So can you tell me how to do what you have suggested?
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37039166
Start with creating a table in the SQL Server database where your reference table is located and getting your SSIS package to import the data into the Excel spreadsheet first.  Let me know when you have that much finished and give me names of both tables being used and the column names that need to be checked.
0
 

Author Comment

by:daintysally
ID: 37039782
Since I am working with sensitive information, I will give sample table and column names.  The table name in the sql server db is RefData and the excel spreadsheet has been imported.  The name of the worksheet is NewData092011.  The column names are listed below for both tables.  I need the stored proc output should let me know that the comparison has identified that "Vehicle Make" and "Vehicle Model" are not included in the 'NewData092011' table.

RefData Tbl column names                           NewData092011 column names (on first row in excel)

FName                                                           FirstName
LName                                                           LastName
Address1                                                      Address1
Address2                                                      Address2
State                                                              State
Zip                                                                 Zip
Phone                                                            Phone
POC                                                               Point of Contact
Vehicle Make
Vehicle Model
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37040459
What is your T-SQL writing experience?  Do you know how to do the basic query for determining which records from NewData092011 do not have matching first name, last name, etc. from RefData?  Have you used Views in SQL Server?  That would be the quick and dirty way to get the SQL for SSIS package.

0
 

Author Comment

by:daintysally
ID: 37040949
No, my t-sql experience is limited.  I need help
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 37041696
If you want a excel spreadsheet macro to be scheduled you can place the call to run the macro in  a sub called

Private Sub Workbook_Open()
End Sub

on the ThisWorkBook page and have the macro close the workbook when it is completed

This way the scheduler only needs to open the workbook and the spreadsheet does the rest

http://support.microsoft.com/kb/265113

Michael
0
 

Author Comment

by:daintysally
ID: 37042848
Ok, I have added the code to "This Workbook" to automatically run my macro, but how to I set it up in SSIS to open the file, save it, then close the workbook?
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 37043632
Is there any reason to use SSIS. Why not just use task scheduler

Michael
0
 

Author Comment

by:daintysally
ID: 37044209
please tell me how
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37044544
Just set up a job in SQL Server.  You already set up a proxy account.  Under SQL Server Agent, there's a Jobs area, right-click and create a new job.  It'll be a command line task--if I were you, I'd write a DOS batch file to open the Excel spreadsheet and call that batch file from the task.
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 37044659
Another thing too is that if you're going to run this on a server as a task, Excel needs to be installed on that server.
0
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 2000 total points
ID: 37048868
Here is a good tutorial on using task scheduler

http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

of course you could always just use

Excel.exe workbook.xls

where workbook.xls is the full path name to the workbook to open. Just use this in the task scheduler wizard (found in the control panel)

Michael
0
 

Author Comment

by:daintysally
ID: 37162765
I've requested that this question be deleted for the following reason:

There has been no activity on this question for days.
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 37162766
I provided a valid answer to the question but got no response from daintysally
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37343879
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question