[ Back to Sprockit home page ]

Sprockit FAQs

Installation

  1. Which versions of SQL Server are supported by Sprockit?

    Sprockit is compatible with SQL Server 2008 R2 and later versions. Only editions that include the SQL Server Agent are supported.

    Support for SSIS package execution uses the SSIS catalog so is not available in versions earlier than SQL Server 2012.

  2. What SQL access permissions are required to use Sprockit?

    To install Sprockit you will need dbo access to the database for your Sprockit installation, and access to create SQL Server Agent jobs.

    To run Sprockit, your SQL Server Agent service account will need:

    • dbo access to your Sprockit database
    • access to create SQL Server Agent jobs

    • execute permission for any process configured in sprockit.Process

  3. How to I migrate my existing ETL routine to Sprockit?

    If you're already using a scheduled agent job to call your ETL, it's easy:

    1. Install Sprockit in a new database, but don't configure the 'Run Sprockit…' job to run on a schedule.

    2. Add a new job step to the end of your current ETL job, of type 'T-SQL' and using the database where you installed Sprockit. In the command pane, enter: 

      DECLARE @jobName NVARCHAR(128) = sprockit.fn_GetProperty('ProcessManagerAgentJobName')
EXEC msdb.dbo.sp_start_job @job_name = @jobName

      This will start a new Sprockit ETL run as soon as your current ETL finishes.

    Now you can migrate processes into Sprockit incrementally, by removing processes from the very end of the routine and configuring them in Sprockit. When you've finished, disable your old ETL job and schedule the 'Run Sprockit…' job directly.

  4. Why do I see messages like The module 'usp_ExecutePackage' depends on the missing object 'SSISDB.catalog… when I run the install script?

    You'll see these messages if you're installing Sprockit on an instance of SQL Server that doesn't have an SSIS catalog. This isn't a problem - if the SSISDB procedures don't exist, Sprockit won't try to use them (but without an SSIS catalog you won't be able to run SSIS packages from Sprockit).

Process configuration

  1. How do I configure an SSIS package as a process?

    Deploy it to the local SSIS catalog, then add an entry for the deployed package to sprockit.Process. You must provide:

    • [DbName] = catalog folder name
    • [SchemaName] = SSIS project name

    • [ProcessName] = package name (including .dtsx extension)

  2. Can I specify views or functions as resources in sprockit.Resource?

    Yes – in fact you should – but configure them as processes as well so they aren't used until their base tables are ready. e.g. For a view:

    • add a new process with the same name as the view;
    • record its base tables/views/functions as input resources for the new process;

    • record the view as the new process's only output resource.

  3. How do I differentiate between a process's input and output resources?

    When you configure resources in sprockit.Resource:

    • set IsInput = 1 for input resources (tables/views/functions read by the process)

    • set IsInput = 0 for output resources (tables updated by the process)

  4. What does an error message like …conflicted with the CHECK constraint "CK__sprockit_Resource__OutputUniqueness" mean when I'm configuring resources?

    It means that you're trying to configure a resource as the output of more than one process. It might already be configured as the output of another process.

    A resource can be the output of only one process.

  5. What does an error message like Circular dependency detected… mean when I'm configuring resources?

    It means that you're trying to configure either:

    • a process output that's already amongst the inputs of an upstream process; or

    • a process input that's already amongst the outputs of a downstream process.

    Circular dependencies are not permitted because they send handlers into infinite loops =).

My install

  1. What version of Sprockit am I running?

    The installed version of Sprockit is recorded in the SprockitVersion property.

  2. Can I rename the process manager job?

    The process manager job is called 'Run Sprockit (SprockitDbName)' by default (where SprockitDbName is the name of the database where you have installed Sprockit. You can change this if you wish but you must also change the value of the ProcessManagerAgentJobName property to match the new job name.

Anything else

  1. What do I do if I can't find an answer to my question here?

    Feel free to drop me an email!

, ,