Deploy SSIS

The SQL Server Integration Services (SSIS) enables you to populate the AIM reporting tables. SSIS provides an environment for updating and managing the tables with minimal impact on the database.

Creating De-Normalized Tables

You must create de-normalized reporting tables before running the SSIS package.

To create de-normalized tables:

1. Go to the following folder:

   {install path}\Report\SSIS Reporting Example

2. Open the DNOMScript.sql file.

   Note: Creating the tables in a database in a separate instance of the SQL server enhances the SSIS performance.

3. Create a new database using the SQL Server Management Studio.

4. Run the DNOMScript.sql script.

Installing and Deploying SSIS Packages

To install and deploy SSIS packages:

5. Go to the following folder:

   {install path}\Report\SSIS Reporting Example

6. Double-click the AVEVA.Reporting.SSIS.Dataload.SSISDeploymentManifest file.

   The package Installation Wizard opens.

7. Select Next.

8. Execute the following based on your infrastructure:

If you want to skip to the Finish the Package Installation Wizard page, select the Finish button.

Note: Use this option if you have backtracked through the wizard pages to revise your choices and have specified all of the required options.

The Finish the Package Installation Wizard page shows the package installation results summary such as:

• Deployed Integration Services project name

• Installed packages

• Configuration files

• Installation location

Important: If you encounter any issues while deploying the package, you can import the package from the Microsoft SQL Server Management Studio.

Note: The package should be imported into SQL Server using the command from the command prompt:
DTUTIL /FILE C:\Program Files\AVEVA\AVEVA NET\Report\SSIS Reporting Example\Default\REP_SSRS.dtsx /COPY SQL;REP_SSRS.

Importing SSIS Packages

To import SSIS Packages:

17. On the Start menu, select All Programs.

18. Under the appropriate version of Microsoft SQL Server, select SQL Server Management Studio.

    The Connect to Server window opens.

19. In the Server type drop-down box, select the arrow, and then select Integration Services.

    The Server name and credentials will be auto-populated.

20. Select Connect.

    Microsoft SQL Server Management Studio opens.

21. In the left pane, expand the Stored Packages node.

22. Right-click File System, and then select Import Package.

    The Import Package windows opens.

23. In the Package location drop-down box, select the arrow, and then select Files System.

24. In the Package path box, select the button, and browse to the package location.

25. Select OK.

    The imported package appears under the File System node in the left pane.

    Note: The package should be imported into SQL Server using the command from the command prompt:DTUTIL /FILE C:\Program Files\AVEVA\AVEVA NET\Report\SSIS Reporting Example\Default\REP_SSRS.dtsx /COPY SQL;REP_SSRS - Where C:\Program Files\AVEVA\AVEVA NET\Report\SSIS Reporting Example\Default\REP_SSRS.dtsx is the path of the package in the installed directory.

Validating SSIS Packages

To validate SSIS packages:

26. On the Start menu, select All Programs.

27. Under Microsoft SQL Server, select SQL Server Management Studio.

    The Connect to Server window opens.

28. In the Server type drop-down box, select the arrow, and then select Integration Services.

    The Server name and credentials will be auto-populated.

29. Select Connect.

30. In the left pane, expand the Stored Packages node.

    

Executing SSIS Packages

This section explains how to connect to the Workhub database through Windows authentication on a specific database schema, while executing the SSIS package to populate the reporting tables.

To configure and execute the package:

31. Open SQL Server Management Studio.

    

32. In the Server type drop-down box, select the arrow, and then select Integration Services.

    The server name and credentials will be auto-populated.

33. Select Connect.

    Microsoft SQL Server Management Studio opens.

34. In the left pane, expand the Stored Package node.

35. Under the MSDB node, right-click REP_SSRS, and then select Run Package.

    

36. In the Execute Package Utility window, select the arrow in the Package Source box to select the location.

37. In the Server drop-down box, click the arrow to select the Server.

38. Select the authentication mode to log on to the Server.

39. In the left pane, select Connection Managers.

    

40. In the right pane, select the DestinationConnection and SourceConnectionManager check boxes.

41. Edit the DestinationConnectionManager Connection String, so that it references the SQL server instance that contains the de-normalized reporting tables.

42. Edit the SourceConnectionManager Connection String, so that it references the SQL server instance with the AIM Workhub database.

43. If you are connecting to the Workhub database, using Windows authentication and on a schema other than 'dbo,' execute the following steps:

    1. In the left pane, select Command Line.

       

    2. In the right pane, select Edit the command line manually.

    3. In the command line, enter the value for SchemaName:

       /DTS "\"\MSDB\REP_SSRS\"" /SERVER localhost /CONNECTION DestinationConnectionManager;"\"Data Source=localhost;Initial Catalog=Workhub_report;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-REP_SSRS-{E6331B89-FBD2-4417-8DFC-16ABA205B88E}DestinationConnectionManager;\"" /CONNECTION SourceConnectionManager;"\"Data Source=localhost;Initial Catalog=workhub_ipe;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-REP_SSRS-{1C77EDAF-04C7-4E6E-B4CA-DE6EAEDB6D39}SourceConnectionManager;\"" /SET "\Package.Variables[User::SchemaName].Properties[Value]";"Test" /CHECKPOINTING OFF /REPORTING V

       Note: In this example, 'Test' is the non-default schema on the Workhub database. If the user variable 'SchemaName' is not defined in the command line, the package assumes 'dbo' as the schema, to connect to the Workhub database.

    4. Select Execute.

       The Package Execution Progress window opens, displaying the progress status and errors, if any.

       Note: The package can also be executed without importing into SQL Server by using the following command: dtexec /File "C:\Program Files\AVEVA\AVEVA NET\Report\SSIS Reporting Example\Default\REP_SSRS.dtsx"

       For this execution, add/change the Destination and source database details by manually opening the package in notepad.

Updating the De-Normalized Tables

To update the De-Normalized tables:

44. Go to the following folder: {install path} \Report\SSIS Reporting Example

45. Open the ViewsScript.sql file.

46. Run the ViewsScript.sql script under the same database that was created while creating de-normalized tables.

Scheduling SSIS Deployment

You must schedule jobs using the SQL Server agent, so that the reporting tables are up-to-date.

To schedule SSIS deployment:

47. On the Start menu, select All Programs.

48. Under the Microsoft SQL Server, select SQL Server Management Studio.

    The Connect to Server window opens.

49. In the Server type drop-down box, select the arrow, and then select Integration Services.

    The server name and credentials will be auto-populated.

50. Select Connect.

    Microsoft SQL Server Management Studio opens.

51. In the left pane, expand the SQL Server Agent node.

    

52. Right-click Jobs, and then select New Jobs.

    The New Job window opens.

53. In the right-pane, type a job name in the Name box (for example, AVEVA Reporting SSIS Job).

54. Go to the right pane, select Steps, and then select New.

    The New Job Step window opens.

55. In the Step Name box, enter a name (for example, AVEVA Reporting SSIS Package Step).

    

56. In the Type drop-down box, select the arrow, and then select SQL Server Integration Services Package.

57. In the Run as box, retain the default SQL Agent Services Account.

58. In the Package Source drop-down box, select the arrow, and then select the same option as selected during the package deployment (SSIS Package Store or File system).

59. If you are not using the File System, select the arrow in the Server drop-down box, and select the server.

60. In the Package box, do any of the following:

    • Enter the Package name.

    • Select the button, and browse to the package location.

    The Select an SSIS Package window opens.

61. Select the SSIS package you have deployed.

62. To close the Select an SSIS Package window and return to the New Job Step window, select OK.

63. In the right-pane, select the Data Sources tab.

64. In the Connection Manager column, select the DestinationConnection and SourceConnectionManager check boxes.

    

65. Go to the Connection String column, and edit both the Connection Strings so that it references the correct machine name.

    Note: The source and destination strings are standard OLEDB connection strings.

66. To close the New Job Step window and return to the New Job window, select OK.

67. In the left pane, select Schedule.

68. To open the New Job Schedule window, select New.

    Note: You can create multiple schedules as required, with setting Alerts, Notifications, or Targets.

69. To test the schedule, right-click the package, and then select Start Job at Step.

    

    The schedule status is displayed.

70. To check the history, right-click the package, and then select View History.