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.
The key settings are to reference java as the executable and then the arguments for the saved Socrata Integration Job. Be sure to run the command in Windows Command Prompt prior to SSIS to ensure that it executes successfully. Sometimes if the windows environment variable for Java have not been setup, it will need to be set up, or the full path to the Java executable will need to be entered for Windows to execute Java programs, like C:\Program Files (x86)\Java\jre1.8.0_66\bin\java.exe.
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.