Best Practices in Bulk Account Creation

We are regularly doing Customer Imports using the bog standard Customer Import, saving the import log as a list and adding the additional Constituencies and Attributes in bulk with "Manage" Utilities.  Automating this would be great but adding Relationships in bulk is a frequent pain.

Looking forward to hearing what you end up with

Thanks.  I'm looking forward to hearing what I end up with as well.  

When you say the "bog standard Custom Import".  To confirm you are specificly talking about the Report/Utility Customer Import Utility, that takes formating a spreadsheet into a format that matches the  Templete files?...

So, I've done that in the past.  And and although it works.  I don't think that this is not going to work for the current project.  If the current project is going to be successful.  I'm going to have to automate this to the "hands off" level.  (No Spreadsheets.  No hand done file copies.) In the work that I've done with that utility over the years I've had reliability problems.  Particularly around upgrades and server changes because of how the files are handled.

So, I want to take this to the next level.

Katie Lachance-Duffy, Nick Reilingh, , Troy Nelson any thoughts?

Tom,

When you say that the standard Constituent Import utility isn't going to suit your project, can I ask why? Like Heath I do a LOT of imports using the supplied utility - but I back it up with some SQL scripts afterwards.

Any automation like you're suggestion would need to be VERY robust - if only to deal with the ensuing duplicates!

Martin 

Yes that utility 

Martin Keen (Past Member)

First great point about the duplicates.  And thanks for your quick reply.

So, Why I'm consurned about the standard utility.  Unlike in the theaters I've worked in, in museum we do a huge amount of "walk up", "day of" orders.  There are lots of consurns about the time it takes to create a Tessitura Consituant record.  To date we gather very few full customer record because all day long is like the 10-15 minutes before curtian in a theater box office. (People want to get into the Museum as soon as they arrive.) That said we are working on gathering more email addresses and names through a variety of mechinisms.  Based on these data sources we will create micro identities with, in some cases, as little information as an email address and name.

For the project, I'm working on we will be doing imports daily, or possibly multiple times daily.  Such that we can send a "Glad you were here emailed" each evening based on these micro identities that have come into the museum each day. These glad you were here emails will be inviting feedback and post-visit engagement with the Museum.  We don't have staff around at these evening times of the day and am I hoping not to use this amount of manual staff time to walk through the standard account creation process 300 - 600 times a year.

So my consurns include timeless of import, use of staff time, and past problems I've had with the environment of Tessitura changing and the process breaking down and needing a bunch of work to get things working again.

I'd love to learn more about the Post process SQL scripts you are using.

I'm also curious to here more about what you think about this idea, and what other might think as well.

Use of a SSIS package would do this automation and that's what it is designed to do. The main thing here is consistency on the input side. 

Troy Nelson

I agree that making consistent data out of quickly entered customer data is going to be one of the challenges.  

I'm going to have to investigate what options I have to use SSIS on RAMP.  

What I've done in the past is leverage the import utilities, but load data into the import work tables myself. The utilities are designed to allow for this without too much difficulty IIRC. That said, the import utilities are also supposed to support automated runs using datafiles dropped into a share location. The question is whether you'd rather transform your input into a datafile format (or attempt to use a custom format specification to map an input format to Tessitura's expected fields), or load your input directly into the work tables in the work table schema. In my case I was already doing record linkage on my data inside the database prior to importing it as constituent records, so the latter option made more sense.

My first scenario for this type of work is for data from inside the Tessitura database.

My second scenario is for data coming in from outside of our database.

I'd like to have a consistent mechanism for both scenarios to keep maintenance and upgrading to a more manageable scope.

Unknown said:

Writing code from scratch that just creates records in T_Customer, T_Eaddress

I'm biased, I suppose, but I've always found this the simplest route. Don't forget rows in TX_CUST_SAL...

Chris Jensen

Do you have a spare set of code hanging around that you can share that does this kind of thing?

I'm not clear what records are necessary to produce a minimally viable customer record.

- T_Customer

- T_Address

- T_Eaddress

- TX_CUST_SALUTATION

I know that I likely have to work with the T_Next_Record

What I'm not clear about is what other records have to be created and updated.  Does someone know of documentation, an existing stored procedure in the database, have code they have tested that they would like to share.

For a "minimally viable" customer record, I am not certain that T_EADDRESS is necessary.  That said, for an "even semi-normal person" record, it would be, as well as T_PHONE.  Otherwise, I think you have the tables needed.  And yes, you would definitely need to work with T_NEXT_ID on this one.

Probably not necessary to remind you, but anytime I work with T_NEXT_ID, I always make sure to use Transaction functionality in SQL to make sure that it does not mess anything up (too badly) if it fails.

Unknown said:

Do you have a spare set of code hanging around that you can share that does this kind of thing?

Anything I've written was built for something specific and not really suitable to use-anywhere situations, however I can pass along some tips:

T_CUSTOMER and TX_CUST_SAL are the barest minimum tables to update re: creating constituents. T_ADDRESS and others may be required if you require that data in your environment.

I believe you can get by with only inserting into these columns in T_CUSTOMER:

(customer_no, cust_type, fname, lname, original_source_no)

I've used these in TX_CUST_SAL:

(signor, customer_no, esal1_desc, esal2_desc,lsal_desc, default_ind, label, business_title, salutation_no)

Re: generating IDs you can always cycle through a cursor, it's not cool but it works; however, I recommend checking out David Woodall's post here:

https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/1621/procedure-frustration/5663

Very elegant. I dynamically create new e-mails from imported payroll code like so (my indents get lost on this page, but hopefully this is still legible):

if (select count(*) from #new_eaddresses) > 0
begin

PRINT 'Adding E-mails.';

declare @number_needed int
,@new_id_no int

select @number_needed = count(*)
from #new_eaddresses -- UPDATE

exec @new_id_no = [dbo].ap_get_nextid_function @type = 'AD'
,@increment = @number_needed

insert into T_EADDRESS
(eaddress_no, customer_no, address, eaddress_type, primary_ind, inactive, market_ind, html_ind)
select
(row_number() over (
partition by 1 order by customer_no
)
) + (@new_id_no -1)
,x.*
from #new_eaddresses x

end
else
begin

PRINT 'No new e-mails today...';

end;

John A. Moskal II

I included t_eaddress because for my projects it was data that I was expecting to have as a minimum requirement in order to even attempt to create the record.

Conversely, I don't expect to have a phone number from this group of constituents.  Do you believe that it's a requirement to create such a record in no actual data that will be filled into the field?

Thanks for the remainder of the transaction functionality of the database.  This is an area that I don't often get into because most of my work has been reporting.

Sorry if my language was confusing.  Not at all.  Assuming no data will be filled in for the constituent T_EADDRESS and T_PHONE are completely unnecessary.  And Chris Jensen is correct, actually, you theoretically would not need T_ADDRESS either since you CAN turn off that requirement.  I just skipped over that because almost every organization of which I am aware has the address field as required for the creation of an account, even if it is just some form of 'INVALID ADDRESS' standard that they use for those who do not want to give their address.

And the Transaction functionality may not be 100% necessary, but I feel safer when I use it for stuff like this.  I have seen people get into some pretty sticky situations when they try to do things like this through the back end and mess up.  When T_NEXT_ID gets off (or I have also seen the IDs in T_TRANSACTION go wrong), it will often prevent ANY user from creating any new account/CSI/address/order/etc...  So I am always extra careful when dealing with that stuff.