Bentley Systems,



Bentley Systems,685 Stockton DriveExton PA 19341eB SSIS Components Installation Version 1.0Published November 2013ConfidentialPlease forward any comments, recommendations, concerns or requests for an updated version of this document to: David.BryantNotice:This information is confidential and may not be distributed without the consent of bentley systems.? DATE\@ "yyyy" \* MERGEFORMAT Bentley Systems, IncorporatedBentley Systems believes the information in this publication is accurate as of its publication date. The information is subject to change without notice. TOC \o "1-2" \h \z 1Introduction PAGEREF _Toc374114042 \h 32Prerequisites PAGEREF _Toc374114043 \h 32.1SQL Server Integration Services 2008 R2 PAGEREF _Toc374114044 \h 32.2eB Queue Service PAGEREF _Toc374114045 \h 32.3SQL Agent Service / Oracle Job Scheduler PAGEREF _Toc374114046 \h 33Installing eB SSIS Components and Database Files PAGEREF _Toc374114047 \h 33.1Run the eB SSIS Components installer: PAGEREF _Toc374114048 \h 33.2Update the data source (or create new data source): PAGEREF _Toc374114049 \h 73.3Add Engines and configure the Queue service: PAGEREF _Toc374114050 \h 93.4Activate the license for Bentley Data Quality Server before the 30 day trial expires. PAGEREF _Toc374114051 \h 124Business Intelligence Design Studio (BIDS) PAGEREF _Toc374114052 \h 145Install the ACE OLE DB 12.0 Provider (optional). PAGEREF _Toc374114053 \h 17IntroductionThis document provides instructions for installing the components required to import/update data in eB using SQL Server Integration Services (SSIS). PrerequisitesSQL Server Integration Services 2008 R2SSIS 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.(SQL.105).aspx (Installing SSIS)SSIS is not required if you are only installing the StageImport database package.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.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.Installing eB SSIS Components and Database FilesThe eB SSIS Components installer will install the following componentseB SSIS IntegrationeB SSIS Components (32 and 64 bit)Execute SSIS Package EngineImport Staged File EngineStaged Import Database FilesStaged Import database packageNote: The installer will restart all eB services running on the machine.Run the eB SSIS Components installer: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.64 bit version of Windows – use eB SSIS Components (x64).msi32 bit version of Windows – use eB SSIS Components (x86).msiNote: 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 ScreenClick the Next button to review the End User License. eB SSIS Components License AgreementBe 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 SetupBegin the installation by clicking Install on the “Verify Installation” screen.Verify Installation ScreenThe Installation progress screen will be displayed. Installation ProgressFinally, click Finish to complete the installationInstallation CompleteUpdate the data source (or create new data source):Open the eB System Management Console (SMC): Start > All Programs > Bentley > eB System Management Console.Expand Machine Name > Data Sources and select the data source to updateClick the Upgrade or Update the Database hyperlink.Data Source DetailsVerify that StagedImport is checked. Click the “Click to Start the Database Update” hyperlink.Data Source UpdateOnce the database update is complete, create a new Community if one does not already exist.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.Stage TablesAdd 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.In the SMC, go to Machine Name > Communities > Community Name > Engines.Click the “Add .NET Assembly Engine” and then Browse to the Engines folder.Engines FolderSelect eB.Engine.ExecuteSSISPackage.dll to add the Engine. Repeat step 7 and this time select eB.Engine.ImportStagedFile.dll. You should now see both engines in the SMC.Engines List in the SMCIn 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.Job TypesSelect 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.Go to Machine Name > Service Manager. Right click on eB Work Queue Service and open the properties dialog.Work Queue PropertiesThe following settings are recommended but are not required.Reset Interval (Sec)10Reduces the time it takes for the Import File engine to startInactivity Timeout (sec)3600Reduces the chance that a long running SSIS job will be terminated because of a timeoutInactivity Threshold1Reduces the chance that a long running SSIS job will be terminated because of a timeoutMax Asynchronous Jobs5Any value greater than 2 will allow an SSIS job and Import File job to run at the same timeClick Ok to save properties. You may need to restart the queue service by right clicking on eB Work Queue Service and selecting Restart.Activate the license for Bentley Data Quality Server before the 30 day trial expires. Go to Machine Name > Licensing to view License Status.License StatusIf Bentley Data Quality Server is not activated, click the License Management Tool hyperlink.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.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 barOpen BIDS. It can be found at All Program > Microsoft SQL Server 2008 R2 > SQL Server Business Intelligence Development Studio.BIDS on the Start MenuOnce 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 ItemsSelect the SSIS Control Flow Items tab. Check the components that you would like to add to the toolbox. SSIS Control Flow ItemsSelect the SSIS Data Flow Items tab. Check the components that you would like to add to the toolbox.Click OK and verify that the components were added to the toolbox.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 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. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download