GPIS – iHistorian Implementation – SQL 2005



GPIS – iHistorian Implementation – SQL 2005

Prerequisites:

1) SQL Server Integration Services

SQL Server Integration Services need to be installed on the SQL server for the SQL Server Business Intelligence Development Studio package to be run via the SQL Job service.

Installation of the SSIS service does not require a reboot after installation if the service was not installed originally.

The installation CD is required.

1. Run the SQL Server Installation Wizard.

2. On the System Configuration Check page, click Next after the scan is complete.

3. In the Name box and in the Company box, enter the appropriate information, and then click Next.

4. Click to select the Integration Services check box, and then click Next.

5. Click Next, and then click Install.

6. When the installation is complete, click Next, and then click Finish.

Reference:



2) iHistorian OLE driver

The iHistorian OLE driver allows the SQL server to communicate directly with the iHistorian server and retrieve data.

The iHistorian installation CD is required.

1) Insert CD into server. If the CD does not AutoRun, right click on the CD drive and select AutoRun

[pic]

2) Click

[pic]

3) Select

[pic]

[pic]

4) Select ONLY the following components

Proficy Historian client Tools

Historian Documentation & Help

Historian Documentation & Help

Historian Sample Applications

OLE DB

OLE DB Driver

OLE DB Samples

[pic]

5) Select

[pic]

6) Enter the IP address of the iHistorian server

[pic]

7) Close this window

[pic]

8) Select the option to restart your computer and click on

3) Create a Linked Server in SQL 2005 Management Studio

The creation of a linked server allows us to retrieve data from iHistorian via SQL T-SQL commands in Query Analyzer and in SSIS packages.

[pic]

1) Open up SQL 2005 Management Studio and connect to the SQL server. Expand Server Objects, Linked Servers, and Providers. Right-Click on ihOLEDB.iHistorian and select Properties.

[pic]

2) Check the following Provider Options:

Level Zero Only

Allow InProcess

Supports ‘Like’ Operator

Click on

[pic]

3) Right-Click on Linked Servers and select New Linked Server

[pic]

4) Enter the following information

Linked Server: IHIST

Provider: iHistorian OLE DB Provider

Product Name: iHistorian

Data Source: IP Address of iHistorian server

Then click on

Installation of SSIS Package

On the SQL server there is a SSIS package named iHistorianTransfer.dtsx. This file is located in either the Visual Studio 2005 Projects folder or in another folder on the server.

To open this file you will need to open SQL Server Business Intelligence Development Studio. There is a short-cut in the Microsoft SQL Server 2005 folder in the Start Menu.

Once you have BIDS open you can open the iHistorianTransfer.dtsx file by clicking on File->Open->File and navigating to the correct location on the server and selecting iHistorianTransfer.dtsx.

You may receive the following error message when opening the file.

[pic]

In the error list in BIDS it will say the following.

Error loading iHistorianTransfer.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

We can ignore this error message. The SSIS file was created with a different Windows User and the security in the package is telling us that there is a problem. This will be addressed when we save the package with any changes that may need to be made.

Once the package is opened we need to make sure that the SQL IP address and userid/password is correct. At the bottom of the design window there is a tab with the caption Connection Manager. In the Connection Manager there is a single connection that we use to connect to the SQL server. When you Right-Click on the connection and select Edit the following window appears.

[pic]

We need to confirm the following information:

Server Name This should be either the IP address or DNS name of the SQL server

User Name: The package is dropping and adding tables, this needs to be SA

Password: Enter the current SA password

Save my Password: Needs to be checked

Database: Select the GPIS database from the drop-down

You then need to click on Test Connection. You should receive the following:

[pic]

We then need to save the package to the SQL server. To do this we click on File->Save Copy of iHistorianTransfer.dtsx As…

[pic]

The Package Location needs to be SQL Server. If you are running BIDS from the SQL server the Server drop down will say (local). The Authentication Type can be Windows Authentication if the user that you are logged in as is part of the Administrators group on the SQL server. Otherwise, select SQL Server Authentication and use the User Name of SA and the current password.

You then need to click on the button to the right of Package Path. The following window will appear:

[pic]

The Package Name will default to Package. Please change this to iHistorian Transfer and select the SSIS Package Folder. Click to continue.

Next, you need to click on the button to the right of Protection Level. The following window will appear:

[pic]

[pic]

From the drop down, you need to select “Rely on server storage and roles for access control”. Click to continue.

The form should now look like this:

[pic]

If it does, you can click to continue.

The SSIS package will now be stored on the MSDB database on the SQL server.

Creating a SQL Job to Run the SSIS Package

Open up the SQL Server 2005 Management Studio. Next, open the SQL Server Agent and then Right-Click on the Jobs folder and select New Job.

[pic]

In the form, give the job a name and a description and select a category from the list provided. Then click on Steps and then click on the button.

[pic]

Give the step a name. From the Type drop down select “SQL Server Integration Services Package”. Leave the Run as selection as “SQL Agent Service Account”.

On the General Tab, select the Package Source as “SQL Server” and from the server drop down select the correct SQL server. Under Log on to the server, select Use SQL Server Authentication and enter the SA user name and password. To select the correct SSIS package to run click the button on the right to bring up the following window.

[pic]

Highlight the correct package and click OK.

You now need to click on Advanced in the menu on the left of the form.

[pic]

Select the following:

On Success Action Quit the job reporting success

For Retry Attempts 3

For Retry Interval 1

On Failure Action Quit the job reporting failure.

Then click on OK.

Next, you need to click on Schedules and then click on New.

[pic]

Enter a name for the schedule and make sure that the schedule type is set to Recurring.

The frequency needs to be set as Daily and recurs every 1 day.

The Daily Frequency needs to be set to occur every 20 minutes.

Click on to continue.

Click on to save the job.

Reference Documents – Error Messages

When running the following query in Query Analyzer:

insert into TEMP_ihtags

select * from openquery(ihist,'select tagname,description from ihtags where tagname not like "%Simulation%"')

You may receive the following error message:

OLE DB provider 'IhOLEDB.iHistorian' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error :7308)

Solution:



Edit the registry as suggested in the GE Fanuc form.

The previous value is Apartment and needs to be changed to Both. SQL Server does not need to be restarted for this change to take affect.

To Delete and existing SSIS package from the SQL Server you need to connect to the Integration Services on the SQL Server.

[pic]

Once you have provided the necessary credentials you can navigate to Stored Packages and under MSDB find the iHistorian SSIS package and delete it.

[pic]

You will then need to re-import the SSIS package and modify the existing SQL job to use the new package.

SQL Commands Used in the SSIS Package

The following SQL T-SQL commands were used in the SSIS package to retrieve the data from iHistorian.

Step 1: Drop TEMP_ihTags

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[TEMP_ihTags]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[TEMP_ihTags]

Step 2: Create TEMP_ihTags

CREATE TABLE [dbo].[TEMP_ihTags](

[tagname] [sql_variant] NOT NULL,

[description] [sql_variant] NOT NULL) ON [PRIMARY]

Step 3: Drop TEMP_ihRawData

if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[TEMP_ihRawData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TEMP_ihRawData]

Step 4: Create TEMP_ihRawData

CREATE TABLE [dbo].[TEMP_ihRawData] (

[tagname] [sql_variant] NOT NULL ,

[timestamp] [datetime] NOT NULL ,

[value] [sql_variant] NOT NULL ,

[quality] [sql_variant] NOT NULL

) ON [PRIMARY]

Step 5: Retrieve Tags from iHistorian

insert into TEMP_ihtags

select * from openquery(ihist,'select tagname,description from ihtags where tagname not like "%Simulation%"')

Step 6: Retrieve Tag Data from iHistorian

insert into TEMP_ihRawData

select * from openquery(iHist,'select tagname,timestamp,value,quality from ihrawdata where samplingmode=currentvalue and rowcount=0')

Step 7: Update Working_IH_Tags

-- Clean out the Live table

truncate table Working_IH_Tags

-- Reload the Working_IH_Tags table

INSERT INTO WORKING_IH_TAGS

SELECT CONVERT(nvarchar(255),TAGNAME), CONVERT(nvarchar(4000),DESCRIPTION)

FROM TEMP_ihTags

Step 8: Import iHistorian Data

-- GPISv2 iHistorian Import - DTS Task Task

Declare @ProcessDate DateTime,

@DataCollectionID integer

-- Takes the current DateTime and converts it to a numeric representation

select @ProcessDate = getdate()

-- Insert a DataCollection record

INSERT INTO DataCollections(DateTime,

InspectionGUID,

OperatorID,

SupervisorID,

ShiftId,

TeamId,

LineGUID,

Obs,

ProductGUID)

SELECT @ProcessDate,

'64F10484-F3EB-4155-98C7-82B440281A93', -- GPIS Form iHistorian

1,

1,

19, -- Admin Shift

13, -- Admin Team

'2D192391-70CB-4051-B68F-629B8ED816B6', -- Float line

'iHistorian Data Load',

'DEFB6B82-E6CA-4BC4-9754-EA09C64BA14F' --'DEFB6B82-E6CA-4BC4-9754-EA09C64BA14F' Non-Product code that is not in the PRODUCTS table

SELECT @DataCollectionID = SCOPE_IDENTITY()

-- Crate TestValues records

INSERT INTO TestValues(DataCollectionID,

DateTime,

TestGUID,

Result, --text

value, --float

Message)

select @DataCollectionID,

@ProcessDate,

TestHMIResultSources.TestGUID,

'',

convert(float,temp_ihrawdata.value),

''

from temp_ihrawdata

join TestHMIResultSources on TestHMIResultSources.VariableName = convert(nvarchar(250),temp_ihrawdata.tagname)

where convert(nvarchar(100),temp_ihrawdata.quality) like '%good%'

................
................

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

Google Online Preview   Download