Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Programmtically executing SSIS
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

43 Posts

Posted - 02/08/2013 :  12:47:43  Show Profile  Reply with Quote
Hi guys, I've been getting a lot of help here. Thank you!! I've learned a lot in the past two weeks I've been working with SQL.

New question, and I know this has been addressed but I can't seem to find anything that will answer my particular question.

I have an entire data flow task that I'll explain, and I need an SSIS import that I've created in BIDS to be executed from Access VBA.

High level - So you see where the data comes from and is going.

A program is used, and a usage statistic of that program is written to a text file. THis happens every day, throughout the day. I open Access db which has a macro and several modules. After running this macro, I go into BIDS, then execute the SSIS package which brings the data into SQL, and send newly imported rows to the backup (thanks James!)

Granular detail - to see all the moving pieces and why I want the SSIS launched automatically through VBA via a macro in Access.

When I open the Access DB, and run the macro, a vba procedure executes importing all the data in a delimited text file into Access. It performs an append query with another table in Access (which is I cant go from .txt to SQL), after the append query, there is an update query to form and assign a unique ID for the lookup transformation. The first table of data that the txt files imported into are deleted (table stays, just records are deleted). Then I go into BIDS, run the SSIS package to get everything into SQL. Then I go back to Access and execute a 'backup' vba procedure which sends the data to new file, and deletes the records in the source database so it's clean and ready for the next batch when I run this process over again.

The reason I want the SSIS package to be launched through VBA is so that I can include it in the macro so the entire process can be automated. I want to see if I can stop having to go into the development environment to run the SSIS package.

Is this possible?

Starting Member

43 Posts

Posted - 02/08/2013 :  12:53:24  Show Profile  Reply with Quote
So you know, I tried to just save the package on my local machine and simply run it through VBA, however, I get this error message: "ERROR: The Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and Select Integration Services."
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/08/2013 :  22:58:24  Show Profile  Reply with Quote
I think what you need is to create a sql server agent for executing ssis package. You can add the code to start the job from access which will execute ssis package

And for running ssis package you need to have an instance of integration services in machine. Best thing is to store package in file system or integration server of the machine and then call it from the sql agent job.

SQL Server MVP

Go to Top of Page

Starting Member

43 Posts

Posted - 02/11/2013 :  09:37:08  Show Profile  Reply with Quote
Thank you. Will try this out.
Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 02/11/2013 :  12:28:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Where is the file? If it is on your machine and you try to access that from the server you might have problems.
SSIS is a server application and so needs an SQL Server licence to run.

To run it from the server I would create an SP to run the dtexec command and call that from VBA - then it will be synchronous and you can get a resiult code from it. Also makes it easier to test the SP and call independently of the macro - make sure you log the call and loging details from the SP so you can see what is executed.
You would also have to enable xp_cmdshell to do this which might be an issue.

Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000