Referencing Talend Contexts and Global Varaible in batch or powershell scripts

Six Stars

Referencing Talend Contexts and Global Varaible in batch or powershell scripts

Hello,

 

I'm trying to take a large excel file with many rows and subsequently create a bunch of mini files derived from the distinct email addresses in that particular column of the spreadsheet. I've been able to successfully do that and during each iteration I'm assigning the 'current' email address and 'current' employee id to respective global variables via tJava.  You can see from the screenshot (attached) how that is operating precisely as I would like.

 

 

Talend_Job.PNG

 

The next step that I'm trying to figure out is how, during each iteration, I can also just email that .xlsx based on the aforementioned global variables and the context variable of the email address and spreadsheet name (.

 

My email server doesn't allow for basic authentication so I can't use the tSendMail component.  I have to use a tSystem component with array command to call a .bat file which then calls a powershell script that contains all of your typical email variables/arguments.

 

My question is:  How do I get that globalMap.get("current_email") and also the context.local_file_path +"/" + ((Integer)globalMap.get("row2.employeeId")) + "_NSF.xlsx" into either the .bat file that tSystem will use OR directly into the .ps1 powershell script.  I think I need to define a parameter block in the .ps1 and then reference those in order from the .bat command but I've never done this type of thing before.

 

My typical array command for the .bat call looks like this:

cmd_path.PNG

 

But if I'm going to pass additional variables at this point, I don't know the syntax to get them here so the .bat file reads them and then passes on to the .ps1 script that this bat file calls.  

The batch file just consists of:

 

@ECHO OFF

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -Command "& 'E:\DirectoryName\emailAttach.ps1'"

 

Can anyone get me started on how to incorporate these context and global variables passed from the tIterateToFlow to  tSystem and batch, powershell scripts?

 

Appreciate the help.

Darin

 


Accepted Solutions
Six Stars

Re: Referencing Talend Contexts and Global Varaible in batch or powershell scripts

Hi Sabrina, 

 

I should've updated this question that I had figured out what I needed to be able to do but it's not relative to calling contexts in the .bat execution script.

 

I needed to be able to pass those contexts through a tSystem component and eventually to a .ps1, external powershell program that will be within the same path as the executable script once deployed.  I cannot send emails using the Talend, tSendMail component because the basic authentication mechanism is not suitable to our exchange server.  So, any time my ETL process requires an email then I have to write a powershell script that does what a tSendMail component might otherwise do (send attachments, pass contexts, etc).  I didn't understand how to do that from Talend when considering both the windows batch file command/scripts and also the powershell .ps1 script syntax.

 

Here's the whole completed job that contains an iterate to flow for both agents and then subsequently each unique supervisor.  I'll take a giant list as input and from that list I need to build specific, smaller lists for each, unique agent contained within and then email that agent that list.  I'll build larger lists for every supervisor and all of their agents and send those attachments via email also from the corresponding tSystem components (em_collectors & em_supers).

AgentSpecificListsEmails.PNG

 

 

During the iteration of each flow, I'm assigning a global variable via the list to capture email address, name and id of each agent.

tJava_Variables.PNG

Now, this is where my confusion came in as I knew how to do all of this to this point but I didn't know how to get those 3 attributes passed to the eventual .ps1 file that will actually send the emails.

That is where the first bit of windows batch scripting came in handy so the first step was to build the tSystem command that would properly encode those paramters to be passed to Powershell:

tSystem_Email.PNG

I had to get all of the slashes right along with all of the escape quotes and so on.  But, I was able to use contexts to keep a dev/test/prod directory as well as capture the proper email attributes for each email.

 

From there I had to understand how to use those -file_attach and -email_to and -collector batch parameters within the powershell program.  I did that by putting a parameter block at the start of the program and listing those paramters in the same order as they were passed from the tSystem command along with a default value in the event there wasn't a value passed from some of the inputs (generalized for sharing).

 

powershell_email.PNG

 

That .ps1 file has to exist within the same production file path in order for Talend to be able to access it using the contexts that I showed above but this solution works extremely well for this use case.  Basically the combination of a tSystem component encoded with proper contexts along with a .ps1 file - I can do what the tSendMail component would be able to do.  This solution will solve a lot of issues since the tSendMail component with Talend isn't functional due to our authentication policy.  

 

This is what I meant by my question of how to pass contexts/parameters/variables from Talend through to a tSystem and be subsequently used external of Talend (powershell).

 

Thanks,

Darin


All Replies
Moderator

Re: Referencing Talend Contexts and Global Varaible in batch or powershell scripts

Hello,

When you build the same job and with the same path, the context variable are still export in the executable script.

Did you edit the script and pass the new value to context variables? You can pass context to the generated scripts by using the arguments: ./jobName_run.sh --context_param VAR1=VALUE1 --context_param VAR2=VALUE2.

Feel free to correct me if I misunderstand your requirement.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Referencing Talend Contexts and Global Varaible in batch or powershell scripts

Hi Sabrina, 

 

I should've updated this question that I had figured out what I needed to be able to do but it's not relative to calling contexts in the .bat execution script.

 

I needed to be able to pass those contexts through a tSystem component and eventually to a .ps1, external powershell program that will be within the same path as the executable script once deployed.  I cannot send emails using the Talend, tSendMail component because the basic authentication mechanism is not suitable to our exchange server.  So, any time my ETL process requires an email then I have to write a powershell script that does what a tSendMail component might otherwise do (send attachments, pass contexts, etc).  I didn't understand how to do that from Talend when considering both the windows batch file command/scripts and also the powershell .ps1 script syntax.

 

Here's the whole completed job that contains an iterate to flow for both agents and then subsequently each unique supervisor.  I'll take a giant list as input and from that list I need to build specific, smaller lists for each, unique agent contained within and then email that agent that list.  I'll build larger lists for every supervisor and all of their agents and send those attachments via email also from the corresponding tSystem components (em_collectors & em_supers).

AgentSpecificListsEmails.PNG

 

 

During the iteration of each flow, I'm assigning a global variable via the list to capture email address, name and id of each agent.

tJava_Variables.PNG

Now, this is where my confusion came in as I knew how to do all of this to this point but I didn't know how to get those 3 attributes passed to the eventual .ps1 file that will actually send the emails.

That is where the first bit of windows batch scripting came in handy so the first step was to build the tSystem command that would properly encode those paramters to be passed to Powershell:

tSystem_Email.PNG

I had to get all of the slashes right along with all of the escape quotes and so on.  But, I was able to use contexts to keep a dev/test/prod directory as well as capture the proper email attributes for each email.

 

From there I had to understand how to use those -file_attach and -email_to and -collector batch parameters within the powershell program.  I did that by putting a parameter block at the start of the program and listing those paramters in the same order as they were passed from the tSystem command along with a default value in the event there wasn't a value passed from some of the inputs (generalized for sharing).

 

powershell_email.PNG

 

That .ps1 file has to exist within the same production file path in order for Talend to be able to access it using the contexts that I showed above but this solution works extremely well for this use case.  Basically the combination of a tSystem component encoded with proper contexts along with a .ps1 file - I can do what the tSendMail component would be able to do.  This solution will solve a lot of issues since the tSendMail component with Talend isn't functional due to our authentication policy.  

 

This is what I meant by my question of how to pass contexts/parameters/variables from Talend through to a tSystem and be subsequently used external of Talend (powershell).

 

Thanks,

Darin

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog