This example shows how to use SQL and SSIS to automate the publishing of a sample budget dataset.
It’s very common that financial data has a primary budget table that countains budget amounts per Fund, Department, Program, Service for a given fiscal period. Usually these records have a code in the budget table that ties to lookup tables for names, descriptions and other supporting information for a Fund, Department, Program and Service. The view created below joins the Fund table to the Budget table to retrieve Fund Name and Type. A SQL view isn’t necessary for SSIS packages, but it is one method of hiding any complex SQL that can then be easily modified without making any changes to the SSIS package that calls it.
Within the Control Flow, an Process Task is added that can be execute Datasync once the Data Flow Task has been completed successfully.
Once the SSIS package has been completed, it is deployed to the SQL Server that will be running it. Note, it is important to set the protection level of the package and job so that the database is able to execute it.
After the Package has been deployed to SQL Server, it will appear in Integration Services Catalog, under whatever folder was created inside of SSIDB.