This article explains how to connect to a MS SQL database from WIMS to pull in data, set up a variable to host the data, and set up a schedule task to have the process automated. The example uses MS SQL server, but will work for any OLE DB connection, such as Oracle.

Step 1. Set up a connection to external data.

Run WIMS and select System Setup > System Tables > External Data Source Setup as shown below:

The External Source Connection Setup is displayed, click the New button:

The ADO Connection String window appears, this is where you need to enter the connection string for your external data. The Name field allows you to create a name for the connection.

To see examples of connection strings, see kb article Q10845. We named our connection string 'Brodhead'. The highlighted areas in the picture below will need to be adjusted for your SQL Native Client connection string. Click the Test button to test the connection string. A prompt will appear letting you know if the connection was successful or not.

Our new connection string is added - thats it!

Step 2. Set up Variable.

The next step is to set up a variable to host the data we want to import. We want the daily influent flow from our Brodhead database. We give the variable an appropriate Name, select Track every Day, and then click the External Source tab.

Click the Link Variable ... check box to reveal the following display:

Click the drop down next to External Source to select our connection string.

Now we need to construct a SQL Query. In our example, we know the data in our external database is daily values so we can use a simple query.

If we had more than one value per day we can summarize the data by using SQL statements such as AVG for average, MAX for maximum, or MIN for minimum - in this case we would start with SELECT AVG(INFLUENTFLOW) FROM .... so that we would get just one value per day. The following shows a simple query for our daily value.

We enter: select INFLUENTFLOW from dbo.Totalizers where SampleDate >= #SD# and SampleDate <= #ED#

The INFLUENTFLOW is the column of data we want in WIMS. The date and time stamp is in the SampleDate column. These columns are in the table dbo.Totalizers. We want to automate this to run once a day so we're going to use two important functions of this setup - Start Date and End Date buttons. After I enter ... where SampleDate >= I click on the Start Date button, or I can just enter #SD#. WIMS will automatically insert start dates and end dates for you. Other functions in WIMS will allow you to enter dates, and they will automatically fill in these two fields for us.

Notice that when we click the Test button, a box (shown int the upper right corner of the picture above) appears with data. We see that our query is exactly correct because the value it brought in was exactly what we see in our database.

The variable is set up, we can now import data

Step 3. Import External Data

If you just want to import data, click Utilities > Import > From External Data Source as shown below.

You will see the following display:

This is one of those functions you can set the Start and End date to import data. Import Data will bring in all variables set up to import external data, and Select Vars will let us import just certain variables. We chose Select Vars and get the following display. We can see the variable number, name and SQL Query.

We select our variable and click the Import Selected Variables to see the following results.

We have populated our variable with data! Now lets automate!

Step 4. Set up Schedule Task.

Click on System Setup > Schedule Tasks

The following screen will be displayed.

Click the New button, and you can create a schedule task. First we name our task 'Import Data' and identify the facility as OPSDWTUTOR. We also would like a message, at least for now, so we know the data was imported. Later on we can edit this task and uncheck the notification box.

Click on the Schedule tab to set up the schedule. We selected DAILY for our frequency to Perform This Task. We know our data is updated at 7:00 AM so we select 07:30 AM to import data.

The next tab we want is What to Do? Here we select RUN IMPORT OF EXTERNAL SOURCES to import our data. 

If we wanted to, we could specify which variables to import, but we will just select ALL VARIABLES IN THIS FACILITY.

Click OK and you are done! You can click the Run Task Now! to populate the external source variables.

We can view our data by clicking Design > Spread Report and click the D button to locate our daily variable.

We see our data is there and it matches what is in the database! We are done!