1 / 19

Integrating SSIS with external applications

Integrating SSIS with external applications. Nauzad Kapadia nauzadk@quartzsystems.com | @nauzadk. Agenda. Interacting with external applications from SSIS Packages Interacting with packages from external applications Typical database maintenance activities.

ahava
Download Presentation

Integrating SSIS with external applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Integrating SSIS with external applications Nauzad Kapadia nauzadk@quartzsystems.com | @nauzadk

  2. Agenda • Interacting with external applications from SSIS Packages • Interacting with packages from external applications • Typical database maintenance activities

  3. Part I – interacting with external applications from ssis packages

  4. Invoking external APIs / Applications • Web Services • Message Queues • Execute Process Task

  5. Invoking Web Services using SSIS demo

  6. Using WebRequest class to invoke web services of make HTTP requests demo

  7. Interacting with MSMQ demo

  8. Monitoring other processes • Interact with the Operating System and other system components thru WMI Tasks • Use WMI Data Reader to • Identify the amount of free/used space on a hard drive on a local or a remote computer system. • Generate a list of all the applications and the application versions installed on a local or remote computer. • Generate a list of all windows services running on a local or remote computer. • Use WMI Event Watcher to • Keep polling a directory for the availability of files which are being written. • Wait for the CPU utilization to come down below a certain threshold to trigger events. • Wait for the server memory to reach a threshold before executing a SSIS package or any process which consumes a lot of server memory. • Wait until a defined windows service to start before proceeding with a specific action.

  9. Using WMIDataReader and WMIEventWatcher demo

  10. Passing data to/from external applications • Use DataReader Source / Destination. • Use SSIS to harness its ETL strengths in conjunction with other applications. • Extract or Import binary data using Import / Export column transformations.

  11. Using DataReaderDestination demo

  12. Using ExportColumn Transformation demo

  13. Part ii – Interacting with packages from external applications

  14. Executing Packages • On local machine • Be aware of licensing violations • Ensure all dependencies are also available on local machine • On a remote machine • Use SQL Agent • Use Web services • Schedule package execution • Create SQL Agent jobs • Create proxy accounts • From SSRS Reports • Enable the SSIS Data Extension in RSReportServer.config and RSReportDesigner.config

  15. Executing Packages on local and remote machines using SQL Agent job demo

  16. Invoking Packages from SSRS Reports demo

  17. Part iii – database maintenance activities

  18. Typical Tasks • Process Dimensions/Cubes • Train Data Mining Models • Create new partitions • Rebuild Indexes • Update Statistics

  19. Thank You nauzadk@quartzsystems.com

More Related