Transcript Integrating SSIS with external applications

INDIA │ 28-30 September 2011
virtual techdays
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
PART I – INTERACTING WITH EXTERNAL
APPLICATIONS FROM SSIS PACKAGES
Invoking external APIs / Applications
• Web Services
• Message Queues
• Execute Process Task
Invoking Web Services using SSIS
DEMO
Using WebRequest class to invoke web services of make HTTP requests
DEMO
Interacting with MSMQ
DEMO
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.
Using WMIDataReader and WMIEventWatcher
DEMO
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.
Using DataReaderDestination
DEMO
Using ExportColumn Transformation
DEMO
PART II – INTERACTING WITH PACKAGES
FROM EXTERNAL APPLICATIONS
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
Executing Packages on local and remote machines using SQL Agent job
DEMO
Invoking Packages from SSRS Reports
DEMO
PART III – DATABASE MAINTENANCE
ACTIVITIES
Typical Tasks
•
•
•
•
•
Process Dimensions/Cubes
Train Data Mining Models
Create new partitions
Rebuild Indexes
Update Statistics
INDIA │ 28-30 September 2011
virtual techdays
Thank You
[email protected]