SSIS API Documentation

Introduction

This document provides comprehensive details on how to interact with the SQL Server Integration Services (SSIS) API. This API allows you to programmatically manage, deploy, execute, and monitor SSIS packages within your environment.

The SSIS API is RESTful and uses standard HTTP methods (GET, POST, PUT, DELETE) and JSON for request and response payloads.

Base URL: https://api.example.com/ssis/v1

Authentication

All API requests must be authenticated using an API Key. The API Key should be included in the Authorization header as a Bearer token.

Authorization: Bearer YOUR_API_KEY

Obtain your API Key from the SSIS Management Console or your system administrator.

API Endpoints

GET /packages

Retrieves a list of all deployed SSIS packages.

Query Parameters

Name	Type	Description	Required
`folder`	string	Filter packages by a specific folder.	No
`project`	string	Filter packages by a specific project within a folder.	No
`pageSize`	integer	Number of results to return per page.	No
`pageNumber`	integer	Page number of the results.	No

Responses

200 OK

Returns an array of package objects:

[
  {
    "id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
    "name": "Sales ETL Package",
    "folder": "Production",
    "project": "DataWarehouse",
    "description": "ETL process for daily sales data.",
    "lastModified": "2023-10-27T10:00:00Z"
  },
  {
    "id": "f9e8d7c6-b5a4-3210-fedc-ba9876543210",
    "name": "Inventory Update",
    "folder": "Staging",
    "project": "Operations",
    "description": "Updates inventory levels from external sources.",
    "lastModified": "2023-10-26T15:30:00Z"
  }
]

401 Unauthorized

If the API Key is invalid or missing.

POST /packages

Deploys a new SSIS package.

Request Body

The request body should be a JSON object containing the package definition.

{
  "name": "New Customer Onboarding",
  "folder": "CRM",
  "project": "CustomerManagement",
  "packageContent": "BASE64_ENCODED_SSIS_PACKAGE_BYTES",
  "description": "Handles the onboarding process for new customers."
}

Responses

201 Created

Returns the details of the newly deployed package:

{
  "id": "a1a2a3a4-b5b6-c7c8-d9d0-e1e2e3e4e5e6",
  "name": "New Customer Onboarding",
  "folder": "CRM",
  "project": "CustomerManagement",
  "description": "Handles the onboarding process for new customers.",
  "lastModified": "2023-10-27T11:00:00Z"
}

400 Bad Request

If the request body is invalid or missing required fields, or if the package content is malformed.

401 Unauthorized

If the API Key is invalid or missing.

409 Conflict

If a package with the same name already exists in the specified folder and project.

GET /packages/{packageId}

Retrieves details of a specific SSIS package by its ID.

Path Parameters

Name	Type	Description	Required
`packageId`	string (UUID)	The unique identifier of the package.	Yes

Responses

200 OK

Returns the package object:

{
  "id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
  "name": "Sales ETL Package",
  "folder": "Production",
  "project": "DataWarehouse",
  "description": "ETL process for daily sales data.",
  "lastModified": "2023-10-27T10:00:00Z",
  "packageContentUrl": "https://api.example.com/ssis/v1/packages/a1b2c3d4-e5f6-7890-1234-567890abcdef/content"
}

404 Not Found

If a package with the specified ID does not exist.

401 Unauthorized

If the API Key is invalid or missing.

PUT /packages/{packageId}

Updates an existing SSIS package. This operation will replace the existing package with the new content.

Path Parameters

Name	Type	Description	Required
`packageId`	string (UUID)	The unique identifier of the package to update.	Yes

Request Body

The request body should be a JSON object containing the updated package definition. All fields are optional, but at least one field must be provided for an update.

{
  "name": "Sales ETL Package - Updated",
  "description": "Updated ETL process for daily sales data.",
  "packageContent": "NEW_BASE64_ENCODED_SSIS_PACKAGE_BYTES"
}

Responses

200 OK

Returns the updated package object:

{
  "id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
  "name": "Sales ETL Package - Updated",
  "folder": "Production",
  "project": "DataWarehouse",
  "description": "Updated ETL process for daily sales data.",
  "lastModified": "2023-10-27T12:00:00Z"
}

400 Bad Request

If the request body is invalid, missing required fields for update, or if the package content is malformed.

404 Not Found

If a package with the specified ID does not exist.

401 Unauthorized

If the API Key is invalid or missing.

DELETE /packages/{packageId}

Deletes an SSIS package by its ID.

Path Parameters

Name	Type	Description	Required
`packageId`	string (UUID)	The unique identifier of the package to delete.	Yes

Responses

204 No Content

Indicates successful deletion. No content is returned.

404 Not Found

If a package with the specified ID does not exist.

401 Unauthorized

If the API Key is invalid or missing.

POST /packages/{packageId}/execute

Executes an SSIS package. This is an asynchronous operation.

Path Parameters

Name	Type	Description	Required
`packageId`	string (UUID)	The unique identifier of the package to execute.	Yes

Request Body

Optional. Allows passing execution-related parameters and environment references.

{
  "parameters": {
    "ParameterName1": "ParameterValue1",
    "ParameterName2": "ParameterValue2"
  },
  "environment": "ProductionEnvironment"
}

Responses

202 Accepted

Indicates that the execution request has been accepted and queued. Returns the execution ID:

{
  "executionId": "09876543-21fe-dcba-9876-543210fedcba",
  "status": "Pending"
}

400 Bad Request

If invalid parameters are provided or the environment is not found.

404 Not Found

If the package with the specified ID does not exist.

401 Unauthorized

If the API Key is invalid or missing.

GET /executions

Retrieves a list of SSIS package executions.

Query Parameters

Name	Type	Description	Required
`packageId`	string (UUID)	Filter executions by package ID.	No
`status`	string	Filter executions by status (e.g., Running, Succeeded, Failed, Cancelled, Pending).	No
`startTimeBefore`	datetime	Filter executions that started before this time (ISO 8601 format).	No
`startTimeAfter`	datetime	Filter executions that started after this time (ISO 8601 format).	No
`pageSize`	integer	Number of results to return per page.	No
`pageNumber`	integer	Page number of the results.	No

Responses

200 OK

Returns an array of execution objects:

[
  {
    "executionId": "09876543-21fe-dcba-9876-543210fedcba",
    "packageId": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
    "packageName": "Sales ETL Package",
    "status": "Running",
    "startTime": "2023-10-27T13:00:00Z",
    "endTime": null,
    "result": null
  },
  {
    "executionId": "1a2b3c4d-5e6f-7890-abcd-ef0123456789",
    "packageId": "f9e8d7c6-b5a4-3210-fedc-ba9876543210",
    "packageName": "Inventory Update",
    "status": "Succeeded",
    "startTime": "2023-10-27T09:00:00Z",
    "endTime": "2023-10-27T09:15:00Z",
    "result": "Success"
  }
]

400 Bad Request

If invalid query parameters are provided.

401 Unauthorized

If the API Key is invalid or missing.

GET /executions/{executionId}

Retrieves details of a specific SSIS package execution by its ID.

Path Parameters

Name	Type	Description	Required
`executionId`	string (UUID)	The unique identifier of the execution.	Yes

Responses

200 OK

Returns the execution object:

{
  "executionId": "09876543-21fe-dcba-9876-543210fedcba",
  "packageId": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
  "packageName": "Sales ETL Package",
  "status": "Running",
  "startTime": "2023-10-27T13:00:00Z",
  "endTime": null,
  "result": null,
  "logEntries": [
    {
      "message": "Package started.",
      "timestamp": "2023-10-27T13:00:01Z",
      "level": "Information"
    },
    {
      "message": "Executing task 'Data Flow Task 1'.",
      "timestamp": "2023-10-27T13:01:05Z",
      "level": "Information"
    }
  ]
}

404 Not Found

If an execution with the specified ID does not exist.

401 Unauthorized

If the API Key is invalid or missing.

Error Handling

The API uses standard HTTP status codes to indicate the success or failure of a request. Error responses will typically include a JSON body with more details:

{
  "error": {
    "code": "INVALID_PARAMETER",
    "message": "The provided 'pageSize' parameter is not a valid integer."
  }
}

Common Status Codes

200 OK: Request succeeded.
201 Created: Resource created successfully.
202 Accepted: Request accepted for processing, but not yet completed.
204 No Content: Request succeeded, but there is no content to return (e.g., successful deletion).
400 Bad Request: The request was malformed or contained invalid parameters.
401 Unauthorized: Authentication failed or was not provided.
403 Forbidden: The authenticated user does not have permission to perform the action.
404 Not Found: The requested resource could not be found.
409 Conflict: The request could not be completed due to a conflict with the current state of the resource.
500 Internal Server Error: An unexpected error occurred on the server.

Rate Limiting

To ensure fair usage and prevent abuse, the API enforces rate limits. If you exceed the rate limit, you will receive a 429 Too Many Requests error.

The current rate limit is 100 requests per minute per API key.

Response headers will include:

X-RateLimit-Limit: The maximum number of requests allowed in the current window.
X-RateLimit-Remaining: The number of requests remaining in the current window.
X-RateLimit-Reset: The time (in UTC epoch seconds) when the current rate limit window will reset.