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.
| 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." |
 |
|
|
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 packageAnd 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-11 : 09:37:08
|
| Thank you. Will try this out. |
|
|
|
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. |
|
|
|
|
|
|
|
|
Programmtically executing SSIS