Hangfire: Add job directly to DB

Question

I'm using Hangfire for background processing. The Hangfire server is running as a web app, I'd like to be able to enqueue a job directly to the database from a SSIS package - is this supported/possible?

Commentate

Answer 1

Better way - write a Web API and call it from your SSIS package. Inside the API write steps to start a Hangfire job.

A hack you can try to do is - Observe the records getting inserted into Hangfire database tables when you en-queue a job from .Net code and try to mimic that from SQL queries. I have never tried it though. good luck!

Giga

This is what I ended up doing (calling a service) so marking as the correct answer – Commentate

@Commentate Sorry, do you mean you ended up writing WEB API & call from SSI or directly "INSERT INTO" HangFire database? I am looking for "INSERT INTO" approch so our database team can utliise this. – Castaway

Can anyone share any tips on how to implement this using SSIS package? – Castaway

@Castaway - sorry, I went the Web API route. Although it looked possible to hack the DB to directly inject jobs into it looked way more work than having a service hooked up. – Commentate

Answer 2

If you really want to add job via DB for some reason, you can try to do this SQL query. After some time (QueuePollInterval configured in Hangfire Server) Hangfire will run this enqueued job

But keep in mind, that this is a "hack" and Hangfire tables could be changed in future. So when able, use other approach

BEGIN TRANSACTION

SET XACT_ABORT ON

DECLARE @queue VARCHAR(20) = 'default'

DECLARE @culture VARCHAR(20) = '"en-US"'

DECLARE @uiCulture VARCHAR(20) = '"en-US"'

-- You can InvocationData and Arguments from some completed job in DB

DECLARE @invocationData VARCHAR(MAX) = '{"t":"YourAppNamespace.JobClass, YourAppAssembly","m":"JobName"}'

DECLARE @arguments VARCHAR(MAX) = '[]'

DECLARE @currentTime DATETIME = GETUTCDATE()

IF (SELECT Version FROM HangFire.[Schema]) <> 7
BEGIN
    PRINT 'Invalid HangFire schema, please review the script'
    SET NOEXEC ON
END

DECLARE @jobId TABLE (ID INT)

INSERT INTO HangFire.Job
(
    StateId,
    StateName,
    InvocationData,
    Arguments,
    CreatedAt,
    ExpireAt
)
OUTPUT inserted.Id INTO @jobId
VALUES
(
    NULL,
    'Enqueued',
    @invocationData,
    @arguments,
    @currentTime,
    NULL
)

DECLARE @unixTimeSeconds VARCHAR(20) = CAST(DATEDIFF(SECOND, {d '1970-01-01'}, GETUTCDATE()) AS VARCHAR(20))

DECLARE @stateId TABLE (ID INT)

INSERT INTO HangFire.State
(
    JobId,
    Name,
    Reason,
    CreatedAt,
    Data
)
OUTPUT inserted.Id INTO @stateId
VALUES
(
    (SELECT Id FROM @jobId),
    'Enqueued',
    'Added via SQL script',
    @currentTime,
    CONCAT('{"EnqueuedAt":"', @unixTimeSeconds, '000","Queue":"', @queue, '"}')
)

UPDATE HangFire.Job
SET StateId = (SELECT Id FROM @stateId)
WHERE Id = (SELECT Id FROM @jobId)

INSERT INTO HangFire.JobParameter
(
    JobId,
    Name,
    Value
)
VALUES
(
    (SELECT Id FROM @jobId),
    'CurrentCulture',
    @culture
),
(
    (SELECT Id FROM @jobId),
    'CurrentUICulture',
    @uiCulture
),
(
    (SELECT Id FROM @jobId),
    'Time',
    @unixTimeSeconds
)

INSERT INTO HangFire.JobQueue
(
    JobId,
    Queue
)
VALUES
(
    (SELECT Id FROM @jobId),
    @queue
)

SET NOEXEC OFF

COMMIT

Xenophon