Versions Compared

Old Version 9

changes.mady.by.user Ben

Saved on

compared with

New Version 10

changes.mady.by.user Ben

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Python 3.6 - 3.10

  • MSDB database / SQLServer DB access

    • We currently only support SSIS package deployments to the MSDB database and not project deployments which deploy to SSISDB database, please advise KADA if you use project deployments against SSISDBif using SSISDB to store package you will need to download each project via SQL Server Management Studio. See <TODO ADD link>

    • The collector will need access to the underlying SQLServer Database with permissions to read the following tables is the SSIS main databases:

      • MSDB.DBO.SYSSSISPACKAGES

      • <SSIS Logging Database>.DBO.SYSSSISLOG where <SSIS Logging Database> is the database configured for SSIS logging

  • Access to K landing directory

  • Access to the KADA Collector repository that contains the SSIS whl

    • The repository is currently hosted in KADA’s Azure Blob Storage. You will be given a SAS token to access the repository. Reach out to KADA Support (support@kada.ai) if you do not have access.

    • Download the SSIS whl (e.g. kada_collectors_extractors_ssis-#.#.#-py3-none-any.whl)

  • Check your SSIS instance port

    • Run the following query and note the local tcp port.

      Code Block
      SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

Downloading SSISDB packages

  1. In SQL Server Studio Manager Go to Integration Services Catalog > Projects.

    Image Added

  2. Right click on a Project > Export…

    Image Added

  3. Save project file

    Image Added

  4. Change the file extraction from .ispac to .zip

    Image Added

  5. Repeat for each project.

...

Step 1: Create the Source in K

...

FIELD

FIELD TYPE

DESCRIPTION

EXAMPLE

server

string

SQLServer server host

Note if the default port is not used append the port to the server name. Example

10.123.123.123\\<SERVICE NAME>,<INSTANCE PORT>

“10.1.18.19”

username

string

Username to log into the SQLServer account

“myuser”

password

string

Password to log into the SQLServer account

 

logging_database

string

Database where the SSIS Logging has been setup

“ssis_logging”

mapping

JSON

Mapping file of data source names against the onboarded host and database name in K

Assuming I have a “myDSN” data source name in powerbi, I’ll map it to host “myhost” and database “mydatabase” onboarded in K, snowflake type references are handled automatically

Code Block
{
        "myDSN": {
            "host": "myhost",
            "database": "mydatabase"
        }
    }

driver

string

This is the ODBC driver, generally its ODBC Driver 17 for SQL Server, if you another driver installed please use that instead

“ODBC Driver 17 for SQL Server”

output_path

string

Absolute path to the output location where files are to be written

“/tmp/output”

input_path

string

Absolute path to the input folder where .dstx files are if uses_ssisdb is true.

The .dstx

Note that within this folder there should be seperate folders for each project and the .manifest/.dtsx/.conmgr (conmgr may or may nor be applicable) files should sit within this folder, for e.g.

/tmp/input
/tmp/input/project1
/tmp/input/project1/my.dtsx
/tmp/input/project1/@Project.manifest
/tmp/input/project1/mycon.conmgr

“/tmp/input”

mask

boolean

To enable masking or not

true

compress

boolean

To gzip the output or not

true

uses_ssissdb

boolean

Are packages deployed to ssisdb or msdb?

false

meta_only

boolean

Do you wish to extract metadata only and don’t want to set up logging for SSIS packages?

false

...