SSIS

SSIS – ForEach looping import files

October 12, 2021 – 2:30 pm

One of the harder things to learn in SSIS is the looping through files and importing them into the DB to be able to work with them. Almost all integrations produce files as [what I am]-[datetime].csv. For example recruiting-Application_Attachments-20211012 00558 is the manifest file extracting the recruiting application attachments and the time-date at the back let’s us know when.

Hopefully the export can take place using one file but don’t count on it, you have to apply looping throughout the whole process, from whatever is doing the extract, through the transfer and onto the load. So if you have a dozen files like above (or 100, 1000, 10000) you need to loop through and transfer.

This walk through from stack overflow is dead on and should get you started in the right direction.

Permalink | Comments Off on SSIS – ForEach looping import files
TCC

TCC – Looping with LRD

April 14, 2021 – 12:56 pm

So looping is great if you have a static set that you are pulling, think a big set of resumes. But what if you are using this as part of an ongoing and looping is needed with a LRD. Fear not, this is how you do it.

Pre-processing:
1st step – com.taleo.integration.client.customstep.paging.PagingPreStep
Arguments:
pagingSize
100000
pagingFilename
[CFGFOLDER]\pagefile.pgn

2nd step – com.taleo.integration.client.customstep.lrd.LRDPreStep
Arguments :
lrdFilename
LRD filename, this will be the name as created by the config in the standard LRD location

Post-processing (no arguments needed):
1st step – com.taleo.integration.client.customstep.count.ExportCountPostStep
2nd to last step – com.taleo.integration.client.customstep.paging.PagingPostStep
Last step – com.taleo.integration.client.customstep.lrd.LRDPostStep

Permalink | Comments Off on TCC – Looping with LRD
TCC

TCC – Table Key

April 14, 2021 – 10:24 am

So sometimes you’re digging through a table and there isn’t an obvious primary key (normally Number). While most of the tables this happens on aren’t usually exported but in case you need to, you can query the table for the primary key by using this projection, then you can hopefully figure out what the key is named.

<quer:projection alias="Validkey" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:key path=""/>
</quer:projection>
Permalink | Comments Off on TCC – Table Key
TCC

TCC – Synchronous vs Asynchronous

April 8, 2021 – 10:24 am

So for whatever reason I have a mental block distinguishing between these two when it comes to the Taleo API. So to finally write it down:

  • TCC uses ‘asynchronous’ transactions (it processes transactions in a queue) while the Taleo Web Services is ‘synchronous’.  
  • The Taleo WebServiceAPI is type Simple Object Access Protocol (SOAP) for XML-based message exchange, not Representational State Transfer (REST).
  • Taleo Web Services is designed for smaller integrations.  
  • The integration limits of each are covered in the appendix of their respective user guides.
  • For more information about the Taleo integration tools and their limits please see:
Permalink | Comments Off on TCC – Synchronous vs Asynchronous
TCC

NetChange Result Files

December 28, 2020 – 12:03 pm

So net-change has given you a bunch of errors, oh well, that’s why we’re here. But it would be nice if net-change would give you the actual identifier instead of instead of a concatenated entity, operation and identifier with a pipe. Oh well, here’s how you pull it in SQL (access) so I don’t have to look up the three functions again.

SELECT SourceCandError.Identifier
,StrReverse(SourceCandError.Identifier) as Rev
,InStr(SourceCandError.Identifier, "|") as FirstPipeLoc
,InStr(StrReverse(SourceCandError.Identifier), "|") as LastPipeLoc
,Right(SourceCandError.Identifier, InStr(StrReverse(SourceCandError.Identifier), "|") -1) as Ident
,Right(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")) as AfterPipe
,Mid(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")+1, Len(SourceCandError.Identifier) - InStr(SourceCandError.Identifier, "|") - InStr(StrReverse(SourceCandError.Identifier), "|")) as Oper
,SourceCandError.Status
FROM SourceCandError;
Permalink | Comments Off on NetChange Result Files
PowerShell

Powershell – Deleting Empty CSV Files

December 7, 2020 – 4:04 pm

Sometimes in integration, you have jobs that run every 5 minutes or so, now these jobs run regardless if there’s data or not and they’ll produce an ’empty’ file. That’s to say there isn’t any usable data in it, just a header row. So you have one file every 5 minutes, that’s 12 an hour or 288 in a day. Some are going to have date, others not. While this can be done with a batch file (elsewhere on this site), Powershell offers a quicker way to do the same thing. Below is a Powershell script that looks through CSV’s and kills ones with only one line of data (header) leaving anything with real data in place.

$path="C:\onedrive_tt\Client\TCC_Touchpoints-Maintenance\scripts\20201207-CleanupTest\response\"
Get-ChildItem $path -Filter *.csv |
ForEach-Object {
$content = $path+"$_"
[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader $content
 while($reader.ReadLine() -ne $null){ $LinesInFile++ }
$reader.Close()
#Write-Host($content+" - "+$LinesInFile)
if ($LinesInFile -eq 1)
{
#Write-Host("File: "+$content+" with "+$LinesInFile+" line will be deleted")
Remove-Item -Path $content -Force
}
}
Permalink | Comments Off on Powershell – Deleting Empty CSV Files
TCC

TCC – Employee Net-Change Musings

November 20, 2020 – 2:51 pm

If you’ve ever done time with TCC you’ll know that the bane of the out of the box integration possibilities is the employee net-change. This is because it’s a marriage of candidate and user and depending on both of those tables to be clean and ready for this is asking a lot.
I ran across a MOS article that actually made clear about the lookups for those two tables. It’s article Doc ID 1047682.1 and here is the PDF defining the candidate/user update columns in the employee net-change sample.

Doc-ID-1047682.1Download
Permalink | Comments Off on TCC – Employee Net-Change Musings
TCC

TCC – Text in Export Spec (warning)

November 9, 2020 – 4:37 pm

A long time ago I posted the code to be able to put test in a column in an export specification. This one in case you need to read the original.
There is an oddity with this in that if you alias “Comment” you will get a sax parsing error. The specific error is: “ORA-00936: missing expression”. I’m not sure why but you have to alias your comment as something other than “Comment”

8/20/2021 – Just found out an alias of Union will do the same thing.

Permalink | Comments Off on TCC – Text in Export Spec (warning)
TCC

TCC – What Templates Are Active

November 2, 2020 – 3:29 pm

So this is specifically for job templates but can be adapted to anything that needs to find out what languages are active for any given situation.

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="RC1704" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobTemplate" locale="en" mode="CSV" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:subQueries/>
	<quer:projections>
		<quer:projection>
			<quer:field path="Number"/>
		</quer:projection>
		<quer:projection>
			<quer:field path="JobCode"/>
		</quer:projection>
		<quer:projection>
			<quer:field path="State,Description"/>
		</quer:projection>
		<quer:projection alias="Lang_fr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr"/>
		</quer:projection>
		<quer:projection alias="Lang_en">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en"/>
		</quer:projection>
		<quer:projection alias="Lang_es">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="es"/>
		</quer:projection>
		<quer:projection alias="Lang_de">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="de"/>
		</quer:projection>
		<quer:projection alias="Lang_it">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="it"/>
		</quer:projection>
		<quer:projection alias="Lang_nl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="nl"/>
		</quer:projection>
		<quer:projection alias="Lang_fr_FR">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr-FR"/>
		</quer:projection>
		<quer:projection alias="Lang_en_GB">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en-GB"/>
		</quer:projection>
		<quer:projection alias="Lang_zh_CN">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-CN"/>
		</quer:projection>
		<quer:projection alias="Lang_ja">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ja"/>
		</quer:projection>
		<quer:projection alias="Lang_pt_BR">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt-BR"/>
		</quer:projection>
		<quer:projection alias="Lang_sv">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sv"/>
		</quer:projection>
		<quer:projection alias="Lang_da">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="da"/>
		</quer:projection>
		<quer:projection alias="Lang_pl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pl"/>
		</quer:projection>
		<quer:projection alias="Lang_ru">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ru"/>
		</quer:projection>
		<quer:projection alias="Lang_fi">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fi"/>
		</quer:projection>
		<quer:projection alias="Lang_cs">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="cs"/>
		</quer:projection>
		<quer:projection alias="Lang_zh_TW">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-TW"/>
		</quer:projection>
		<quer:projection alias="Lang_ko">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ko"/>
		</quer:projection>
		<quer:projection alias="Lang_th">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="th"/>
		</quer:projection>
		<quer:projection alias="Lang_ms">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ms"/>
		</quer:projection>
		<quer:projection alias="Lang_ca">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ca"/>
		</quer:projection>
		<quer:projection alias="Lang_tr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="tr"/>
		</quer:projection>
		<quer:projection alias="Lang_sk">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sk"/>
		</quer:projection>
		<quer:projection alias="Lang_et">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="et"/>
		</quer:projection>
		<quer:projection alias="Lang_no">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="no"/>
		</quer:projection>
		<quer:projection alias="Lang_bg">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="bg"/>
		</quer:projection>
		<quer:projection alias="Lang_hr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hr"/>
		</quer:projection>
		<quer:projection alias="Lang_hu">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hu"/>
		</quer:projection>
		<quer:projection alias="Lang_ro">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ro"/>
		</quer:projection>
		<quer:projection alias="Lang_el">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="el"/>
		</quer:projection>
		<quer:projection alias="Lang_sr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sr"/>
		</quer:projection>
		<quer:projection alias="Lang_lt">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="lt"/>
		</quer:projection>
		<quer:projection alias="Lang_pt">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt"/>
		</quer:projection>
		<quer:projection alias="Lang_sl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sl"/>
		</quer:projection>
		<quer:projection alias="Lang_in">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="in"/>
		</quer:projection>
		<quer:projection alias="Lang_ar">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ar"/>
		</quer:projection>
	</quer:projections>
	<quer:projectionFilterings/>
	<quer:filterings>
		<quer:filtering>
			<quer:equal>
				<quer:field path="State,Description"/>
				<quer:string>Active</quer:string>
			</quer:equal>
		</quer:filtering>
	</quer:filterings>
	<quer:sortings>
		<quer:sorting ascending="true">
			<quer:field path="JobCode"/>
		</quer:sorting>
	</quer:sortings>
	<quer:sortingFilterings/>
	<quer:groupings/>
	<quer:joinings/>
</quer:query>
Permalink | Comments Off on TCC – What Templates Are Active
PowerShell

TCC & Powershell – CSV to Set Filters

October 26, 2020 – 4:41 pm

If you’ve been working with TCC for any length of time you’ll know the purple squirrel, rainbow unicorn and pot at the end of the rainbow is being able to set the filters based on a CSV. This ability would mark a boon for automating ad hoc integrations.

Now there are options, Python could pull it off but Python doesn’t live and play on a stock server I run across 95% of the time. Powershell does however, well it usually lives there, not sure how much play is involved.

In any case, it turns out we can do this whole process in Powershell without a whole bunch of headache. 

#Declare variables
$sourcePath = "..\..\scripts\mappingFiles\mappingContestNumberToJobCode.csv"
$targetPath = "..\..\scripts\mappingFiles\mappingFilterList.txt"
$targetContent = @()
$outputContent = New-Object System.Text.UTF8Encoding $false
#Loop through source file to build filter
Import-Csv $sourcePath | ForEach-Object {
    $targetContent += "<quer:string>$($_.Requisition)</quer:string>"
}
#Write out file as UTF8 no BOM
[System.IO.File]::WriteAllLines($targetPath, $targetContent, $outputContent)
#Update export specificaitons with the new filter set
((Get-Content -path ..\..\scripts\RequisitionExport\RequisitionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionExport\RequisitionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExport_sq.xml;
((Get-Content -path ..\..\scripts\SourcingRequestExport\SourcingRequestExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\SourcingRequestExport\SourcingRequestExport_sq.xml;
((Get-Content -path ..\..\scripts\CandidateMatchExport\CandidateMatchExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\CandidateMatchExport\CandidateMatchExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExport_sq.xml;
Permalink | Comments Off on TCC & Powershell – CSV to Set Filters

« Previous Page — Next Page »

« Previous Page — Next Page »