integrating ssis with external applications
DESCRIPTION
Integrating SSIS with external applications. Nauzad Kapadia [email protected] | @nauzadk. Agenda. Interacting with external applications from SSIS Packages Interacting with packages from external applications Typical database maintenance activities. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/1.jpg)
virtual techdaysINDIA │ 28-30 September 2011
Integrating SSIS with external applicationsNauzad Kapadia
[email protected] | @nauzadk
![Page 2: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/2.jpg)
Agenda
• Interacting with external applications from SSIS Packages
• Interacting with packages from external applications
• Typical database maintenance activities
![Page 3: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/3.jpg)
PART I – INTERACTING WITH EXTERNAL APPLICATIONS FROM SSIS PACKAGES
![Page 4: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/4.jpg)
Invoking external APIs / Applications
• Web Services• Message Queues• Execute Process Task
![Page 5: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/5.jpg)
DEMOInvoking Web Services using SSIS
![Page 6: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/6.jpg)
DEMOUsing WebRequest class to invoke web services of make HTTP requests
![Page 7: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/7.jpg)
DEMOInteracting with MSMQ
![Page 8: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/8.jpg)
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.
![Page 9: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/9.jpg)
DEMOUsing WMIDataReader and WMIEventWatcher
![Page 10: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/10.jpg)
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.
![Page 11: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/11.jpg)
DEMOUsing DataReaderDestination
![Page 12: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/12.jpg)
DEMOUsing ExportColumn Transformation
![Page 13: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/13.jpg)
PART II – INTERACTING WITH PACKAGES FROM EXTERNAL APPLICATIONS
![Page 14: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/14.jpg)
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
![Page 15: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/15.jpg)
DEMOExecuting Packages on local and remote machines using SQL Agent job
![Page 16: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/16.jpg)
DEMOInvoking Packages from SSRS Reports
![Page 17: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/17.jpg)
PART III – DATABASE MAINTENANCE ACTIVITIES
![Page 18: Integrating SSIS with external applications](https://reader036.vdocuments.us/reader036/viewer/2022081511/5681676e550346895ddc576c/html5/thumbnails/18.jpg)
Typical Tasks
• Process Dimensions/Cubes• Train Data Mining Models• Create new partitions• Rebuild Indexes• Update Statistics