SQL Server Integration Services (SSIS) Package Implementation¶

PAFnow Data Processing uses the PAFnow SSIS Package to prepare your event log for the subsequent analysis in Power BI and to automatically create the necessary data model. If your event log is not saved in your Microsoft SQL Database but instead exists as a *.csv-file, the PAFnow SSIS Package will even import it into the Microsoft SQL database for you!

Implementation with SQL Server Management Studio (SSMS)¶

1) Open the SQL Server Management Studio.

2) Expand the node Integration Services Catalogs > SSISDB in the Object-Explorer of SQL Server Management Studio and create a new folder by right-clicking on SSISDB and selecting Create Folder.

3) Open the folder and right-click on Projects.

4) Select Deploy Projects.

5) The Integration Services Deployment Wizard opens. Click Next at the bottom of the Introduction-page.

6) On the Select Source-page, you need to set the deployment model to Project Deployment. Click on Browse under Path and select the file containing the PAFnow SSIS Package. Your server must have access to the PAFnow SSIS Package. Finish the source selection by clicking on Next.

7) Enter the server name into the text box Server Name on the Select Destination-page or click the Browse-button to select the server instance. Then select your authentication and press Connect. Finish the destination selection by clicking Next.

8) Review the deployment action summary on the Review-page. After verification, click the Deploy-button to execute the deployment action.

9) After completing the deployment, you will see the Results-page. Review the results for each step in the deployment process on this page. Click on Save Report to save the deployment report or on Close to close the Integration Services Deployment Wizard.

10) You have successfully implemented the PAFnow SSIS Package and can now find it under Integration Services Catalogs > SSISDB > Folder Name > Projects > PAFnow-Dataprocessing > Packages.

Known Issues with SQL Server 2016¶

It is a known issue that package execution can fail on SQL Server 2016 due to deployment via the wrong Integration Services Deployment Wizard. To make sure you are deploying with the right wizard, go to your SQL Server installation directory and got to 130\DTS\Binn and execute ISDeploymentWizard.exe (130 stands for SQL Server Version 13.0, i.e. SQL Server 2016).

Configuration with SSMS¶

1) Open SQL Server Management Studio (SSMS) and connect to the instance of the SQL Server containing the Integration Services Catalog.

2) Create a database with your event log on the SQL Server instance. If you already have one, skip this step. To create a database, right-click on Databases > New Database. Then enter the name of your database.

3) Before the PAFnow SSIS Package can be executed, it has to be configured. Expand the node Integration Services Catalog > SSISDB in the Object-Explorer of SQL Server Management Studio. Navigate to the folder where the PAFnow SSIS Package was implemented.

4) Select the PAFnow SSIS Project (PAFnow-DataProcessing) via right-click and go to Configure.

5) Define the following parameters by clicking on the "…"-buttons on the right.

• CaseAttributeCSVPath: If you have saved your case attributes in an additional *.csv-file, then specify the path here and make sure that the server has access to the *.csv-file. Otherwise skip this step.
• CaseAttributesSeparator: If you have saved your case attributes in an additional *.csv-file, indicate here how the data in the CaseAttributes.csv are separated from each other.
• CaseAttributesTable: If your case attributes are already available in an SQL Database, specify the name of the database here.
• EventLogCSVPath: If your event log is available as a *.csv-file, then specify the path here and make sure that the server has access to the *.csv-file. Otherwise skip this step.
• EventLogSeparator: If your event log is available as a *.csv-file, indicate here how the data in your EventLog.csv are separated from each other.
• EventLogTable: If your event log is already available in a MS SQL Database, specify the name of the table here.
• SchemaIn: Specifies the table schema where the event log resides and temporary staging tables are being stored (first character must not be a digit)
• SchemaOut: Specifies the table schema of where the output tables should reside (first character must not be a digit)
• TablePrefix: Specifies a table prefix, e.g., DepartmentX_EventLog (first character must not be a digit)
• UserName (mandatory): Provide your PAFnow username (email address your licence is assigned to)
• UserPassword (mandatory): Provide your password to authenticate with PAFnow

6) The parameters are set to NULL by default. To change this, go to Edit Value under Value and define the parameter in the input box on the right.

7) Define parameters for your server and database by switching to Connection Managers.

8) Enter your database under Initial Catalog and your server under ServerName by clicking on the "…"-button on the right.

8a) Select the LogSqlConnection and specify ServerName and Initial Catalog as above. This connection determines where your logs will be stored. They are needed should there ever be a problem with the PAFnow SSIS Package and you need to contact support.

9) To finish the configuration of the project, click OK.

Execution with SSMS¶

1) Open SQL Server Management Studio (SSMS) and connect to the instance of the SQL Server containing the Integration Services Catalog.

2) Expand the node Integration Services Catalogs > SSISDB in the Object-Explorer of SQL Server Management Studio. Navigate to the folder where your PAFnow SSIS Package was implemented. Choose the PAFnow SSIS Package PAFnow-DataProcessing-Package.dtsx via right-click and go to Execute.

3) Check all specified parameters and change them, if necessary, via the "..."-button. To start the execution of the PAFnow SSIS Package, click OK.

4) A window will appear. If you want to see the report overview for more information on the execution of the PAFnow SSIS Package, choose Yes. Otherwise choose No and finish the process.

Exporting Logs¶

Should you need to contact PAFnow Support about an issue with the PAFnow SSIS Package you might be asked for a log of your execution.

Logs are stored in the dbo.sysssislog table on the database you specified for the LogSqlConnection (See step 8a of Configuration with SSMS). If you did not specify a valid connection, no logs will be produced. In that case, make sure to execute the package again, with a valid logging connection, before you proceed.

All runs of the SSIS package will be stored in that table, all of them having their own unique executionid. If you have executed the PAFnow SSIS Package only a few times, you can simply export the whole dbo.sysssislog table. If the package runs on a regular basis the table might be to large to export.

In that case you can use the following script to select only the entries created by latest execution:

DECLARE @LatestExecutionId uniqueidentifier; 
SET @LatestExecutionId = (SELECT TOP 1 [executionid] FROM [dbo].[sysssislog] ORDER BY [starttime] DESC);

SELECT [source], 
    [message],
    [starttime]
FROM [TESTDB].[dbo].[sysssislog]
    WHERE [executionid] = @LatestExecutionId;

After executing the query in SSMS right click anywhere on the result and select 'Save Result As...'

Please attach the resulting file to your support inquiry