UserVariables Activity - PassLastSyncDate

Tornquist · Post by **Tornquist** » Tue Jun 04, 2013 2:17 pm

I am trying to create a system to keep track of the last sync date of a table. All the fields in the table have a updated date field and I would like to filter the input based on those two dates instead of filtering after the database connector with a transformer. Because I would like to filter before the connected, a parameter is needed to input the date.

I've been trying to have a Server Job run to grab the last updated date and store it in $UserStatus. From the Sequential Job I then grab that value and attempt to pass it to the job that I need to filter the input on by setting the passed variable name to the same thing as the parameter. This isn't working. The variable isn't being received and I'm doubtful that the rest of the process is correct.

I've tried to figure this out on my own, but so many different sources have partial steps to the process that a comprehensive look is difficult to achieve. I'd appreciate any help in completing this and if another method is better, those recommendations would be welcome as well. The only limitation is that I cannot touch the source server except to request data.

Thanks,

Nathan

chulett · Post by **chulett** » Tue Jun 04, 2013 2:32 pm

You don't really need a UserVariable stage for this and it should be rather straight-forward if you are certain you are setting USERSTATUS properly.

Your Sequence job should have two Job Activity stages in it, one to run the Server job that puts the value into USERSTATUS and then a following job that has a "LastSyncDate" job parameter, yes? Off the top of my head, all you should have to do to pass one to the other is go into the second Job Activity stage and using the External Parameter Helper accessed via the ellipsis (...) select the $UserStatus variable in the list of candidate values from the preceeding Job Activity stage. Do this while you have focus on the LastSyncDate job parameter value field.

Is that what you've been doing? If you are passing it correctly and it isn't "being received" then you'd need to share details with us as to exactly how you are setting USERSTATUS in the Server job.

Tornquist · Post by **Tornquist** » Tue Jun 04, 2013 2:42 pm

It's hard for me to tell if I've set it up correctly. This is what I've done:

Sync_GetTime -- Server Stage
Load_Date (Sequential File) -> Set_Stage_Variable (Transformer) -> Save_Backup (Sequential File)

I have the final sequential file to keep the transformer happy. Within the transformer I have a stage variable that is set by the input from the first sequential file. There will only ever be one row returned.

On that stage I also have a After Routine that runs "Call DSSetUserStatus(InputArg)" and within that job I have the after-job routine set to Set_Stage_Variable.StageVar.

----------------------------------------------------------------

Sequential Job

Sever Job (Sync_GetTime) --> Test_Parameter_Job (Parallel with input parameter LastRunDate and Sync_GetTime.$UserStatus -- name here matches server job activity name from within the sequential job)

----------------------------------------------------------------

Parallel Job

LastRunDate parameter with default to "TEST"

My output printed Set_Stage_Variable.StageVar which means the string was passed, not the value of the date. Do I need to do anything special with this, or is it possible for me to pass a timestamp to UserStatus and the load that in the parallel job?

------------------------------------------------------------------

Thanks for your help!

-Nathan

chulett · Post by **chulett** » Tue Jun 04, 2013 2:54 pm

Tornquist wrote:Sync_GetTime -- Server Stage
Load_Date (Sequential File) -> Set_Stage_Variable (Transformer) -> Save_Backup (Sequential File)

I have the final sequential file to keep the transformer happy. Within the transformer I have a stage variable that is set by the input from the first sequential file. There will only ever be one row returned.

On that stage I also have a After Routine that runs "Call DSSetUserStatus(InputArg)" and within that job I have the after-job routine set to Set_Stage_Variable.StageVar.

There are too many moving parts in this Server job. There shouldn't be any "after" components here, neither after stage nor after job is needed. If you've searched here on the subject you should have found mention of creating an "interlude" to the DSSetUserStatus function and all that means is a custom routine you write that takes in a single argument then calls that function and passes the input value back out. This can be leveraged directly in a transformer with or without a stage variable.

There are some good examples in this thread of the routine code and how to use it. See if that helps and then we can go from there.

chulett · Post by **chulett** » Wed Jun 05, 2013 7:24 am

Any luck with this Nathan?

Tornquist · Post by **Tornquist** » Wed Jun 05, 2013 8:05 am

Yes, that information was very useful. I had read that thread before, but I had misunderstood some of the information. I am successfully passing information from one job to another.

I have a few more questions but I can ask those in another thread if that is preferred. They relate to the ultimate use of the information, not the obtaining of it.

chulett · Post by **chulett** » Wed Jun 05, 2013 8:14 am

Excellent news! Since this topic is resolved, please mark it as such using the button on the top of the page and then start a new thread for your other questions.

Tornquist · Post by **Tornquist** » Wed Jun 05, 2013 8:18 am

Done. Thanks again for all of your help.

chulett · Post by **chulett** » Wed Jun 05, 2013 10:13 am

Forgot to mention one bit of information here - the issue with doing those "after" routines is the fact that anything "after" has no access to row data flowing through the job. I'll admit that I have no idea what Set_Stage_Variable.StageVar is but I doubt it changes that fact.