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

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!!
daintysallyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Molly FaganApplications Team SupervisorCommented:
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
daintysallyAuthor Commented:
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
Molly FaganApplications Team SupervisorCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

daintysallyAuthor Commented:
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
Molly FaganApplications Team SupervisorCommented:
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
daintysallyAuthor Commented:
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
Molly FaganApplications Team SupervisorCommented:
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
daintysallyAuthor Commented:
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
Molly FaganApplications Team SupervisorCommented:
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
daintysallyAuthor Commented:
No, my t-sql experience is limited.  I need help
0
Michael FowlerSolutions ConsultantCommented:
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
daintysallyAuthor Commented:
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
Michael FowlerSolutions ConsultantCommented:
Is there any reason to use SSIS. Why not just use task scheduler

Michael
0
daintysallyAuthor Commented:
please tell me how
0
Molly FaganApplications Team SupervisorCommented:
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
Molly FaganApplications Team SupervisorCommented:
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
Michael FowlerSolutions ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
daintysallyAuthor Commented:
I've requested that this question be deleted for the following reason:

There has been no activity on this question for days.
0
Michael FowlerSolutions ConsultantCommented:
I provided a valid answer to the question but got no response from daintysally
0
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.