With the SFTP ports in BizTalk 2013, we are using the log function. BizTalk appends each SFTP log to the end of the existing log file.
So I created a Powershell Script to take an array of directory names, and run the same re-run and delete logic for each.
I also include the logic to delete a file over a certain retention period (see also shorter code for that in prior post: “Powershell to Delete Old Files“).
# Neal Walters - 09/05/2017
# Script: RenameSFTPLogsToAddDate.ps1
# Purpose: Rename a file such as "receive_internal.txt" to
# so that each day has it's own separate file of SFTP messages.
# Also purge files over $retentionDays
# Use Task Scheduler to schedlue this script
# to run once late a night or early morning.
$fullname = $_.FullName.ToString();
$dirname = $_.Directory.ToString();
$filename = $_.Name.ToString();
$filenameOnly = [io.path]::GetFileNameWithoutExtension($filename)
$ext = [io.path]::GetExtension($filename) # this includes the ., for example .txt
#If filename contains an underscore, then we think it has a date in it.
#If no date found, we want to rename the file, otherwise leave it as it was.
# looking for 20 as the century… part of date 2017, 2018, 2019 etc…
# if (-Not ($filenameOnly.toString().Contains(“_20”) ) )
if (-Not ($filenameOnly -Match “_20”) )
Write-Host “OldName $fullname”
$content = Get-Content $_.FullName
$formatDate = get-date -f _yyyy_MM_dd
The one thing I didn’t account for (yet), is the case where different files can have different EDI delimiters. Technically, you should look for the end of the ISA segment to find the delimiters, and use those in the RegEx match. For now, I’m assuming the field delimiter is * and the segment delimiter is the tilda (~).
I was archiving the EDI files in BizTalk with the filename set to “%datetime%_%MessageID%_EDI.txt”. I decided it would be better to name the files COMPANYNAME_DOCTYPE_ORDERNO_ORDERDATE_%datetime%_%MessageID%_EDI.txt.
NOTE: I could have done this logic in a custom C# BizTalk Pipeline, but decided to do it after the fact with a more simple Powershell than would be easier for administrative staff to maintain and update.
Sample 1 – Just test the parsing
With this sample, you can copy the contents of a file into the $ediText string, and test.
#Note subsituted " with `" in the string to escape the quotes within quotes issue
$ediText = "ISA*00* *00* *ZZ*MYCUSTOMER*ZZ*MYCOUNTRY*170823*1610*U*00401*000000117*0*T*:~GS*PO*BTS-SENDER*RECEIVE-APP*170823*1610*117*T*00401~ST*850*0117~BEG*00*NE*391949**20170828~N1*BY*DELIVERY-ADDRESS~N1*ST*DELIVERY-ADDRESS~N3*1420 MAINSTREET DR~N4*DALLAS*TX*12345~PO1*1*5.00*EA*4.350**IN*106889~PID*F****SAND MIX ( SSM80 )~PO1*2*1.00*etc...~";
$CompanyID = [regex]::match($ediText,'.*ISA\*.*?\*.*?\*.*?\*.*?\*.*?\*(.*?)\*.*').Groups.Value
$OrderNum = [regex]::match($ediText,'.*BEG\*.*?\*.*?\*(.*?)\*.*').Groups.Value
$OrderDate = [regex]::match($ediText,'.*BEG\*.*?\*.*?\*.*?\*.*?\*(.*?)[~\*].*').Groups.Value
$EdiDocType = [regex]::match($content,'.~ST\*(.*?)[~\*].*').Groups.Value
Write-Host "CompanyID = $CompanyID";
Write-Host "OrderNum = $OrderNum";
Write-Host "OrderDate= $OrderDate";
Write-Host "EdiDocType= $EdiDocType";
Having a filename like this will make it faster to search the archives for certain types of orders or files from a certain partner, or do do quick counts, based on the filename alone. For example, how many files did we get from XYZ company yesterday and today? This could be done in BizTalk with BAM as well, but my current client opted out of the overhead and complexity of BAM, especially since BizTalk was (for the most part), just passing the files around, not creating them.
The variable $EdiDocType above represents something like and 850, 855, 856, 810, 997, etc…
I might add one more feature. Many of the trading partner don’t use name, but some Dun number, phone number, or other ID number. I might have a lookup table to translate the code to a shortname that represents that trading partner.
It selects and executes any command on a file or set of files.
With forfiles, you can run a command on or pass arguments to multiple files. For example, you could run the type command on all files in a tree with the .txt file name extension. Or you could execute every batch file (*.bat) on drive C, with the file name “Myinput.txt” as the first argument.
With forfiles, you can do any of the following:
Select files by an absolute date or a relative date by using the /d parameter.
Build an archive tree of files by using variables such as @FSIZEand @FDATE.
Differentiate files from directories by using the @ISDIRvariable.
Include special characters in the command line by using the hexadecimal code for the character, in 0xHH format (for example, 0x09 for a tab).
I recently showed a VBScript to Archive/Move xml files to a subfolder. This is often needed when you have been archive or storing 1000s of XML files, and the the directory/folder is very slow to open due to the large number of files. Now, we will do it in Powershell.
Create the subfolders ahead of time. With some minor improvements we could do that in the code, but I was in a hurry today when I needed this…
This does the following:
1) Select all files starting with “201604” (in my case, the files began with yyyymmdd.
2) pipe that into a ForEach loop
3) Run the “Move” commandlet
4) the filename in the loop is the $_ symbol
5) Then you build the destination directory $_ again is the iterator of the loop, i.e. the FileName object, so we can get it’s directory and “Name”. There, we insert the 2016_xx for the month.
So yes, you can make this a lot fancier, but it’s a start…
I had a .bat file with 50 lines or more, and many of them had disk paths. We were migrating this to production, so I did “replace all” commands to change all the paths to production SAN/Server names. But then, I knew some of the paths existed, and some didn’t. So I wanted to find all the paths that didn’t exist, so either:
1) I could fix the filename, or
2) Create the path on the disk
So I needed to parse the file looking for file/path names. At first I tried RegEx, but then decided that just using “Split” was faster in my case. (Sometimes you just want to get the job done in the shortest amount of time.)
The following works when you have a prefix on each directory path. I’m sure there are variations you could make on this depending on your filenames. I’m only looking for lines that have .exe, because the .bat file is running various C# program to process the files.
Sample file Test.bat:
line1 Small.exe \\MyServer\Messages\Dir1 and more words
line2 Biggertest2.exe \\MyServer\Messages\Dir1 parm2 \\MyServer\Messages\Dir2 parm4
With BizTalk, we often archive files in a sub directory. Personally, I would rather archive to a SQL database with a database column, but that takes a little more architecture and sales. So in the meantime, many clients continue to write files to disk. There are frequently clean-up jobs that delete files over x days old.
However, when there are multiple thousands of a files in any directory, it can take a long time to open that directory, and display the files, especially when you are accessing it from a remote computer.
The $DaysBack parameter can be set to non-zero if you want to only group files over x days old into sub-directories.
</script></noindex> files (for example BizTalk archive XML files)
#into subfolders based on date
$SourceDir = "C:\TestFiles\"
$DestinationDir = "C:\TestFiles\"
$DaysBack = 0
$files = get-childitem $SourceDir *.*
Write-Host "File COunt= $($files.Count)"
foreach ($file in $files)
$NewSubDirectory = $DestinationDir + "" + $file.LastWriteTime.Date.ToString('yyyy-MM-dd')
#Create $NewSubDirectory if it doesn't already exist
if (!(Test-Path $NewSubDirectory))
New-Item $NewSubDirectory -type directory
if ($DaysBack -gt 0)
If($file.LastWriteTime -lt (Get-Date).adddays(-1 * $DaysBack).date)
Move-Item $file.fullname $NewSubDirectory
Move-Item $file.fullname $NewSubDirectory
You will need full write/rename access to run this script. You can specify a UNC name in the $SourceDir and $DestinationDir variables.
Sometimes you need to mass replace all the text string for all files in a directory, or at least all files matching some file mask.
Here’s a quick sample that I put together.
As a BizTalk consultant, I deal with data coming in from customers or trading partners. Sometimes, that data needs to be scrubbed. We were doing multiple rounds of testing, and the trading partner was going to put a fix in place in a few days, but in the meantime, I was having to hand edit each and every file manually, before putting the file into BizTalk. I was really getting tired of that process and wrote the script below.
</script></noindex> Fix various issues in the certain EDI files
$path = "c:\MyPath\"
$files = get-ChildItem $path -filter "850*.edi"
#$files #use this to just display all the filenames
foreach ($file in $files)
Write-Host "`n`nfixing file= $($file.Name)"
$filetext = Get-Content $file.FullName -Raw
# The -Raw (line above) option brings all text into a string, without dividing into lines
Write-Host "Old Text in file: $($file.Name)"
#example of regular text
$filetextNew = $filetext -replace "Texas", "TEXAS"
#example of changing EDI tags
$filetextNew = $filetextNew -replace "\^WACO", "\^DALLAS"
$filetextNew = $filetextNew -replace "\^EXCELLENT", "\^EU"
$filetextNew = $filetextNew -replace "\^MODUSE", "\^MU"
$filetextNew = $filetextNew -replace "\^LIGHTUSE", "\^LU"
$filetextNew = $filetextNew -replace "\^HEAVYUSE", "\^HU"
Set-Content $file.FullName $filetextNew
I was modifying an EDI file, so I wanted to make sure that the string I was modifying started with the caret symbol. So for example, I really wanted to change “^MODUSE” to “^MU”. Since that caret symbol has special meaning in RegEx (Regular Expressions), I had to put the backslash in front of it as an escape character. So I added the first line to change “Texas” to “TEXAS” to show that the backslahs and caret symbol are not needed for normal text replacement.
The one bug I had in the code above was specifying $file.Name instead of $file.FullName. It almost drove me crazy. It seemed to be returning the filename itself, rather than the contents of the file; probably because that file didn’t exist in the current directory in which the PowerShell script itself was running.
In the past, I used to use a utility called “BK-Replace’Em”, which is now called by the more generic name “Replace Text”. You can download a free copy from EcoByte here. It can do the same thing as above, without writing any code. The only thing is you need to be able to download and install it on your server, and I didn’t want to do that on the various servers that I’m currently working on.
I was playing with SQLPS for the first time and wanted to save my samples for future use.
Import-Module SQLPS –DisableNameChecking
Write-Host "Done with Import-Module"
Write-Host "`n`nList of Instances (not working?)"
Get-ChildItem | Select instancename
Write-Host "`n`nList of Databases on Local Server default instance"
cd SQLSERVER:\sql\localhost\DEFAULT\Databases # specify "DEFAULT" if you have no instance name
Get-ChildItem | Select name
Write-Host "`n`nAlternate List of Databases on Local Server default instance"
Invoke-SQLcmd -Server '.' -Database master 'select name, database_id, create_date from sys.databases' | Format-Table
Write-Host "`n`nList of tables in NealDemo Database"
Write-Host "`n`nRun some SQL Command"
Invoke-Sqlcmd -Query "SELECT @@VERSION, db_name();"
#$sqlpath = "SQLSERVER:\sql\localhost\DEFAULT\Databases`nealDemo\Tables"
Done with Import-Module
Name Root Description
---- ---- -----------
DAC SQLSERVER:\DAC SQL Server Data-Tier Application
DataCollection SQLSERVER:\DataCollection SQL Server Data Collection
SQLPolicy SQLSERVER:\SQLPolicy SQL Server Policy Management
Utility SQLSERVER:\Utility SQL Server Utility
SQLRegistration SQLSERVER:\SQLRegistration SQL Server Registrations
SQL SQLSERVER:\SQL SQL Server Database Engine
SSIS SQLSERVER:\SSIS SQL Server Integration Services
XEvent SQLSERVER:\XEvent SQL Server Extended Events
DatabaseXEvent SQLSERVER:\DatabaseXEvent SQL Server Extended Events
SQLAS SQLSERVER:\SQLAS SQL Server Analysis Services
List of Instances
List of Databases on Local Server default instance
Name : EMPLOYEES
Name : NealDemo
Name : ReportServer
Name : ReportServerTempDB
Name : VideoGenerator
Alternate List of Databases on Local Server default instance
WARNING: Using provider context. Server = localhost.
name database_id create_date
---- ----------- -----------
master 1 4/8/2003 9:13:36 AM
tempdb 2 4/16/2015 11:42:24 PM
model 3 4/8/2003 9:13:36 AM
msdb 4 2/20/2014 8:49:38 PM
ReportServer 5 12/11/2014 5:42:06 PM
ReportServerTempDB 6 12/11/2014 5:42:07 PM
NealDemo 7 1/8/2015 1:31:34 PM
VideoGenerator 8 1/12/2015 2:08:16 PM
EMPLOYEES 9 1/20/2015 2:21:54 PM
List of tables in NealDemo Database
Schema Name Created
------ ---- -------
Audit AuditAllExclusions 2/4/2015 8:21 AM
Audit AuditBaseTables 2/4/2015 8:21 AM
Audit AuditDetail 2/4/2015 8:21 AM
Audit AuditDetailArchive 2/4/2015 8:21 AM
Audit AuditHeader 2/4/2015 8:21 AM
Audit AuditHeaderArchive 2/4/2015 8:21 AM
Audit AuditSettings 2/4/2015 8:21 AM
Audit SchemaAudit 2/4/2015 8:21 AM
dbo Employee 2/4/2015 3:55 PM
Run some SQL Command
WARNING: Using provider context. Server = localhost, Database = NealDemo.
Column1 : Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Column2 : NealDemo
1. The CD shouldn’t be needed, but if you are running Powershell in a different directory in ISE, it can be helpful.
2. Get-ChildItem returns all files in the current directory with the mask *.xml
3. Then “ForEach” matching file, do what is in the curly brackets of the Foreach-Object loop. If you know what you are doing you can pipe without the Foreach, but I like to break it down, so I can add the debug Write-Host statements and run a simulation run (by commenting out the actual Rename-Item statement) before the final rename.
4. The -replace is the keyword that tells us that we are doing a RegEx replace. Here I’m changing a date like this: YYYYMMDDHHMMSS_xxxx to YYYYMMDDHHMM. (This was a requirement of the a customer. The downside is you could have duplicate files on the rename if more than one file was created in the same minute; but that was not our issue.)
I’m using the () to capture the YYYYMMDDHHMM string and then the `$1 substitutes that string back into the new filename. The grave-accent mark is the escape character to tell Powershell that I don’t want to insert a variable by the name $1 (which would have a value of null or empty-string, because I don’t have such a variable. $1 is used only with the Powershell replace, it’s not a real Powershell variable.
5. Write-Host shows the new filename.
6. Do the actual rename. Just comment out this line with # at the beginning to do a simulation run and verify the names.