TaskCentre - Dynamically Naming a File

My goal was to save a file using the current date; for example:

foo_MMDDYYYY.txt

In the Save As File object, set the Filename property as so:

Week16_{=Month(Date())}{=Day(Date())}{=Year(Date())}.txt

Here is a screeshot:

Troubleshooting TaskCentre

On occasion, TaskCentre will stop running or return cryptic errors like:

"Cannot run task “Monthly Mailer” because the server has no available slots"

"Monthly Mailer" is the name of one of my tasks - substitute the name of a task, etc.

The first thing to check to see if the service is running. If it is, try restarting it - many times it has been the fix.

Also, I recommend setting up a simple task in TaskCentre that sends you an email everyday. If it does not arrive you know something is up.

Resetting TRANS records

We are testing some G/L Integration; one of the challenges is once we run our Month-End Procedures they are no longer available for testing again.

One way to resolve this is simply to take a backup of the database before running the Month-End Procedures and restoring it if you want to try it again.

A short-cut method, which is working for us is to update the TRANS.Posted field like so:


/*
Testing G/L Integration
set the TRANS.Posted value to 2 to re-integrate or setup transaction for month-end procedures
when the month end procedures are run the value get changed to 4
*/
update
trans
set
posted = 2
where
year(transaction_date) = 2009

Creating a tab delimited file with TaskCentre

GOAL: On a monthly basis, create a membership roster containing the mailing address in a tab delimited format.

Task contains the following tools (screenshoot above):

1. Event, Schedule
2. Input, Database Query
3. Format, Format As Text
4. Output, Save As File

The technique that I wanted to share is how to insert the Tab character into a text document. After some trial and error I found that the following worked for me in the Detail section of the Format As Text object:

{=ThisStep.RecordSource("ID")}{=vbTab}{=ThisStep.RecordSource("prefix")}{=vbTab}...

Objective Internet Information

http://www.pff.org/digital-economy-fact-book/index.html

Testing PayFlow Pro Gateway v4 in iMIS

The new gateway is manditory September 1st, 2009

If your iMIS installation is greater than 15.0.3.2362 then you are all set.

On your testing server, update the Host URLs to:

pilot-payflowpro.verisign.com

http://www.vandamme.com/blog.aspx?id=2850&blogid=236

TaskCentre Triggers and SQL Views

It appears that you can create a trigger on a view within TaskCentre but this is not true. It does not work they way you might think or expect. There is an INSTEAD OF triggering action on SQL views that is better described here.

For TaskCentre purposes you will want to always base your task on a table INSERT or UPDATE. I spent a number of hours trying to figure out how to use a view with no success.

I am simply not satisfied with this. What about creating a view that updates a table periodically? I am off to try out the theory...

Setting Up an iMIS Testing Database: Part 2

After completing testing database update one of our developers complained that their login was not working on the testing database. I think I should describe some of our setup before getting into the solution.

We have two seperate database servers - one for production and one for testing. On each server we have a database dedicated to iMIS and another for custom web application. The custom web application database needs to retrieve information from the iMIS database; to make this possible we create a few cross-database views. These views simplify the work for our developers...

We have the same users and logins on both the testing and production databases. When I restore a backup copy of production over the testing database I end up with orphaned logins.

To fix the orphaned logins I ran the following script I found on SQLServerCentral.com

WARNING: This works for me but please read the Microsoft Books Online before proceeding. iMIS user logins are updated and corrected as part of the DB Repair Utility; the logins I am 'fixing' here are manually created for specific purposes.

-- fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @v_dbuser
while (@@FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @v_dbuser
set @sql = 'sp_change_users_login ''auto_fix'',' + @v_dbuser
exec sp_executesql @sql
fetch c1 into @v_dbuser END
CLOSE C1
DEALLOCATE C1

Setting Up an iMIS Testing Database

I support more than one iMIS installation therefore being able to setup a testing copy of iMIS quickly is important to our project progress. In many situations you need a recent copy of production data setup on a testing database.

The details of setting up a testing application server and installation are for another posting. This overview assumes you already have testing infrastructure in place.

Here is the quickest and most reliable method I know to create get a testing database setup.

/*

Name: iMIS Testing Database Setup
Author: Travis Dunbar
Date: November 6, 2008
UpdatedL April 8, 2009

Purpose: To quickly update a testing database without having to login and manually change settings.

Assumptions:
- The production and testing versions of iMIS (15.0.3.2289) are already setup
- SQL Server 2005

Steps:
- Create a SQL backup of your iMIS database
- Move the backup file to the testing database server
- Restore database on testing server; I had to stop my iMIS application server to do the restore, the app server automatically creates a connection to the database
- Login to iMIS DB Repair Utility
- Connect to the testing server and testing database
- Run "Prepare iMIS Database" and "Reset All Permissions" functions
- Run the script below in SQL Server Management Studtio

Now users can login to the testing copy of production, quickly identify they are in the testing copy, and even submit testing credit card payments.

*/

UPDATE CCAuthAcct
SET CCAuthURL='test-payflow.verisign.com'
WHERE CCAuthURL = 'payflowpro.verisign.com'
--select * from ccauthacct WHERE CCAuthAcctDescription='YOUR ACCOUNT NAME'

UPDATE SYSTEM_PARAMS
SET SHORTVALUE='###### iMIS TEST DATABASE ###### RESTORED: ' + CAST(getdate() AS CHAR(12))
WHERE PARAMETERNAME='System_Control.SystemName'
-- select * from SYSTEM_PARAMS WHERE PARAMETERNAME='System_Control.SystemName'

iMIS TaskCentre; New Member Communication - Part 1

TaskCentre is by far my favorite module of iMIS. Today I deployed my first task into production. Over the next few weeks I plan to blog about my experiences; I have not been able to attend any training and the user community does not appear to have a forum.

Goal:
We have a series of phone, email and mailing communications we send to a new members during their first year. I am using TaskCentre to automate this business process as much as possible. There are a handful of emails, a telephone call follow-up, and a couple of mailing pieces. There are business intelligence and data quality aspects of my tasks; I will not go into detail about them in this thread.

Strategy:
Break the marketing plan deliverables into a series of phases or parts. The first task will fire when a new member 'hits' iMIS. Subsequent tasks will be scheduled to fire at predetermined intervals as requried by the marketing plan.

Implementation:
I created a trigger on a user-defined window/table where we flag that a new member application has been approved. In my organization we requrie a few items for membership including dues payment, curriculum vitae, diplomate info, licensing, credentials, etc.

In iMIS, upon entering the Application Approved date:
- Welcome
- An email is sent to the email address within the customer record
- An EMAIL activity is created when the email is sent; a reference of the date and time the email was sent
- A CALL activity is created if there is no email address; assigned to Member Services Manager
- Phone Call from Member Services
- A CALL activity is created with a tickler date 49 days out; assigned to Member Services Manager

The CALL activities, when assigned to a user appear in the View Tasks interface of iMIS. So far this task addresses three components of the marketing plan:
- An email is sent to the new member with their website login information.
- A CALL activity is created if we do not have an email
- A follow-up CALL activity with a 7 week (49 days) tickler date is created

That is where we are at now.