bentley systems, · web viewssis must be installed from the sql server 2008 r2 installation media....

Bentley Systems,685 Stockton Drive

Exton PA 19341

eB SSIS Components Installation Version 1.0Published November 2013

ConfidentialPlease forward any comments, recommendations, concerns or requests for an updated version of this document to:

David.Bryant

NOTICE:THIS INFORMATION IS CONFIDENTIAL AND MAY NOT BE DISTRIBUTED WITHOUT THE CONSENT OF BENTLEY

SYSTEMS.

© 2023 Bentley Systems, Incorporated

Bentley Systems believes the information in this publication is accurate as of its publication date. The information is subject to change without notice.

Last printed 5/22/2006 9:05:00 PMLast saved 5/22/2006 9:05:00 PM

CONFIDENTIAL DRAFT 1.0

1 Introduction............................................................................................................................................ 32 Prerequisites.......................................................................................................................................... 3

2.1 SQL Server Integration Services 2008 R2........................................................................................32.2 eB Queue Service........................................................................................................................... 32.3 SQL Agent Service / Oracle Job Scheduler.....................................................................................3

3 Installing eB SSIS Components and Database Files.............................................................................33.1 Run the eB SSIS Components installer:...........................................................................................33.2 Update the data source (or create new data source):........................................................................73.3 Add Engines and configure the Queue service:................................................................................93.4 Activate the license for Bentley Data Quality Server before the 30 day trial expires...........................12

4 Business Intelligence Design Studio (BIDS)........................................................................................145 Install the ACE OLE DB 12.0 Provider (optional)..................................................................................17

2


1 IntroductionThis document provides instructions for installing the components required to import/update data in eB using SQL Server Integration Services (SSIS).

2 Prerequisites2.1 SQL Server Integration Services 2008 R2

SSIS must be installed from the SQL Server 2008 R2 installation media. On a 64 bit machine, you should install both the 32 bit and 64 bit version of SSIS. The reason is that some SSIS components will only run in 32 bit mode. You can install only the 64 bit version but you will be unable to use the 32 bit components such as the Excel Datasource Component. All Bentley SSIS components will run in 32 or 64 bit mode.

http://msdn.microsoft.com/en-us/library/ms143731(SQL.105).aspx (Installing SSIS)

SSIS is not required if you are only installing the StageImport database package.

2.2 eB Queue Service The eB SSIS Integration can be installed on the main eB Application Server or on a server that serves only the eB Queue Service.

2.3 SQL Agent Service / Oracle Job SchedulerThis is not a true prerequisite for installation. However, the SQL Agent Service (for SQL Server) or Job Scheduler (for Oracle) must be running in order for the Import Staged eB Data task to work. You will not be able to import data via SSIS without this task and these services running. To be clear, the SQL Agent Service must be running on the primary eB database server which most likely will not be where SSIS is installed.

3 Installing eB SSIS Components and Database FilesThe eB SSIS Components installer will install the following components

eB SSIS Integration

eB SSIS Components (32 and 64 bit) Execute SSIS Package Engine Import Staged File Engine

Staged Import Database Files

Staged Import database package

Note: The installer will restart all eB services running on the machine.

3.1 Run the eB SSIS Components installer:1) Locate the installer in \Server Installation\eB SSIS Components. Run the appropriate eB SSIS Components

msi for the version of Windows that you are running.a. 64 bit version of Windows – use eB SSIS Components (x64).msib. 32 bit version of Windows – use eB SSIS Components (x86).msi

3

http://msdn.microsoft.com/en-us/library/ms143731(SQL.105).aspx


Note: The 64 bit installer will install both the 32 and 64 bit SSIS components if it detects that both the 32 and 64 bit version of SSIS is installed.

The Welcome screen is displayed.

eB SSIS Components Welcome Screen

2) Click the Next button to review the End User License.

4


eB SSIS Components License Agreement

3) Be sure that you can accept the End User License Agreement before clicking Next to proceed to the Custom Setup screen. This screen tells you what is being installed. You cannot disable any features. The installer will only install eB SSIS Integration if SSIS is installed. Also, there is no need to specify an installation folder because the SSIS components must be installed in a sub folder of the SSIS installation. The StagedImport database files and eB Engines must be installed in a subfolder of the eB installation. Click Next to proceed.

Custom Setup

4) Begin the installation by clicking Install on the “Verify Installation” screen.

5


Verify Installation Screen

5) The Installation progress screen will be displayed.

Installation Progress

6


6) Finally, click Finish to complete the installation

Installation Complete

3.2 Update the data source (or create new data source):1) Open the eB System Management Console (SMC): Start > All Programs > Bentley > eB System

Management Console.2) Expand Machine Name > Data Sources and select the data source to update3) Click the Upgrade or Update the Database hyperlink.

7


Data Source Details

4) Verify that StagedImport is checked. Click the “Click to Start the Database Update” hyperlink.

Data Source Update

5) Once the database update is complete, create a new Community if one does not already exist.6) You can verify that the stage tables were created by viewing them in the SQL Management Studio. These are

the tables that you will populate using SSIS.

8


Stage Tables

3.3 Add Engines and configure the Queue service:These instructions will configure both Engines to run on the same machine. However, you can run them on different machines by configuring each Job Type to run on a different machine. This allows you to execute SSIS packages on a machine that is different than the primary eB Application Server. It also allows you to execute the Import Staged File engine on third machine, if necessary. This could be helpful depending on where the files are located and where the eB repository is located.

1) In the SMC, go to Machine Name > Communities > Community Name > Engines.2) Click the “Add .NET Assembly Engine” and then Browse to the Engines folder.

9


Engines Folder

3) Select eB.Engine.ExecuteSSISPackage.dll to add the Engine. 4) Repeat step 7 and this time select eB.Engine.ImportStagedFile.dll. You should now see both engines in the

SMC.

Engines List in the SMC

5) In the SMC go to Machine Name > Work Queue. Make sure your community is checked. Then right click on your community and open the properties dialog.

10


Job Types

6) Select the SSIS and Import Staged Files job types. If you prefer, you can configure one machine to run SSIS jobs and another machine to run Import Staged Files jobs. Click ok to save and close the dialog.

7) Go to Machine Name > Service Manager. Right click on eB Work Queue Service and open the properties dialog.

11


Work Queue Properties

8) The following settings are recommended but are not required.

Reset Interval (Sec) 10 Reduces the time it takes for the Import File engine to startInactivity Timeout (sec) 3600 Reduces the chance that a long running SSIS job will be

terminated because of a timeoutInactivity Threshold 1 Reduces the chance that a long running SSIS job will be

terminated because of a timeoutMax Asynchronous Jobs 5 Any value greater than 2 will allow an SSIS job and Import File

job to run at the same time

9) Click Ok to save properties. You may need to restart the queue service by right clicking on eB Work Queue Service and selecting Restart.

3.4 Activate the license for Bentley Data Quality Server before the 30 day trial expires. 1) Go to Machine Name > Licensing to view License Status.

12


License Status

2) If Bentley Data Quality Server is not activated, click the License Management Tool hyperlink.

13


3) Select Bentley Data Quality Server and click the Activate button.

The installation is now complete. See sections below for information on using the SSIS components and the Execute SSIS Package engine.

4 Business Intelligence Design Studio (BIDS)The design environment for SSIS 2008 R2 is called Business Intelligence Design Studio (BIDS). It is essentially a special version of Visual Studio 2008. Before you can use the eB SSIS Components in BIDS, you must add them to the tool bar

1) Open BIDS. It can be found at All Program > Microsoft SQL Server 2008 R2 > SQL Server Business Intelligence Development Studio.

14


BIDS on the Start Menu

2) Once in BIDS, go to Tools > Choose Toolbox Items… It may take as long as 1 minute to load the dialog since the list of available items is generated dynamically.

Choose Toolbox Items

3) Select the SSIS Control Flow Items tab. Check the components that you would like to add to the toolbox.

15


SSIS Control Flow Items

4) Select the SSIS Data Flow Items tab. Check the components that you would like to add to the toolbox.

16


5) Click OK and verify that the components were added to the toolbox.

5 Install the ACE OLE DB 12.0 Provider (optional).Microsoft only distributes the 32 bit data provider for Excel/Access. We have found that the ACE OLE DB 12.0 provider works better than the out of the box provider from Microsoft and also supports 64 bit architectures. Since

17


the eB Queue Service runs as a 64 bit process, we recommend installing the ACE OLE DB 12.0 provider if you need to work with Excel/Access in your SSIS packages. It can be downloaded here.

http://www.microsoft.com/en-us/download/details.aspx?id=13255

18

http://www.microsoft.com/en-us/download/details.aspx?id=13255

bentley systems, · web viewssis must be installed from the sql server 2008 r2 installation media....

Documents