virtual techdays india │ 28-30 september 2011 integrating ssis with external applications nauzad...
TRANSCRIPT
virtual techdaysINDIA │ 28-30 September 2011
Integrating SSIS with external applications
Nauzad [email protected] | @nauzadk
Agenda
• Interacting with external applications from SSIS Packages
• Interacting with packages from external applications
• Typical database maintenance activities
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.
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.
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
Typical Tasks
• Process Dimensions/Cubes• Train Data Mining Models• Create new partitions• Rebuild Indexes• Update Statistics