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

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.

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

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.

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.

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.

No, it does not need to be in a spreadsheet for output.  So can you tell me how to do what you have suggested?

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.

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

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.

No, my t-sql experience is limited.  I need help

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

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?

Is there any reason to use SSIS. Why not just use task scheduler

Michael

please tell me how

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.

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.

I've requested that this question be deleted for the following reason:

There has been no activity on this question for days.

I provided a valid answer to the question but got no response from daintysally

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.