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

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Programmtically executing SSIS

Author  Topic 

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-08 : 12:47:43
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.

Scenario:
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?

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-08 : 12:53:24
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 22:58:24
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
http://visakhm.blogspot.com/

Go to Top of Page

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-11 : 09:37:08
Thank you. Will try this out.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-02-11 : 12:28:01
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
   

- Advertisement -