Socrata was acquired by Tyler Technologies in 2018 and is now the Data and Insights division of Tyler. The platform is still powered by the same software formerly known as Socrata but you will see references to Data & Insights going forward.

Using a SSIS to write to a Socrata Dataset

aliciatb

aliciatb

    This example shows how to use SQL and SSIS to automate the publishing of a sample budget dataset.

    SQL Server

    View that joins RevenueBudget to Fund for Fund Name and Type

    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.

    SQL View

    SSIS Package

    Dataflow

    • The first step involves creating a Dataflow that first makes an OLE DB Connection to the database. Then it is possible to select any table or view on that database.
    • The destination dataset also contains a Count column to make it easy to create rollups and interesting visualizations so a Derived Column transform creates that column and sets value to 1.
    • A Flat File transformer creates a CSV with quotes around text fields to handle any values that may contain commas.

    SSIS screenshot dataflow

    Control Flow that calls the Dataflow and then executes Datasync command

    Within the Control Flow, an Process Task is added that can be execute Datasync once the Data Flow Task has been completed successfully.

    SSIS screenshot workflow

    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.

    SSIS screenshot task settings

    Package Deployment to SQL Server

    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.

    SSIS screenshot of package deployment

    SQL Server Agent

    After the Package has been deployed to SQL Server, it will appear in Integration Services Catalog, under whatever folder was created inside of SSIDB.

    Package Execution Report after execution

    SSIS screenshot dataflow

    Resources

    Downloads