For all of you out there that have been doing scripting inside of SmartConnect, we have an excellent (although belated) New Year’s gift for you. We have assembled a collection of the most requested scripts over the last year and have commented and formatted each script in detail. They are now all bundled together and available for no charge in our knowledge base:
Download
This collection of 20+ scripts covers a broad range of functions, from moving and renaming a file to reading entries off of the event log. Each script is written in VB.Net and has a large header section at the beginning that explains what the function of the script is and how to implement it.
Let’s walk through one of the scripts, MoveFileAddDate, to see how it works and in what ways it can be used effectively. Now, if any of you have used the SmartConnect tasks you know that there is already a prebuilt move file task. The drawback to this task is that the file name is going to be static. This script lets us add a date and/or time stamp onto the filename when we move it.
‘– Beginning of Script
‘—————————————————————————————
‘ Script Name: MoveFileAddDate
‘ Created On: 9/23/2009
‘ Author: Chris Hanson
‘ Purpose: This script moves an existing file from one location to another and
‘ adds a date stamp to the file name.
‘ History:
‘—————————————————————————————
‘—————————————————————————————
‘ Requirements: This map can be located in any task script location. The source file
‘ must exist and the date stamp that is being added to the file name
‘ must not contain any illegal characters.
‘—————————————————————————————
‘—————————————————————————————
‘ Setup: The dateFormat string determines how the Datetime object that contains
‘ the current date and time is parsed to a string. The default value
‘ will return a date without any slashes (ex. 092309). The sourceFile
‘ should be changed so that it points to an existing file, and the
‘ destFile can be set to anything that is desired (as long as it does
‘ not include illegal characters).
‘—————————————————————————————
‘———————————– CONFIGURATION ————————————-
Dim dateFormat As String = “MMddyy”
Dim sourceFile As String = “C:SmartConnectSample FilesFixed_Asset_General.csv”
Dim destFile As String = “C:SmartConnectSample FilesFixed_Asset_General.csv”
‘ Note: Do not include the date in the destination file name, the script will add it
‘ just before the extension (Ex. Fixed_Asset_General-092309.csv) in the code below.
‘—————————————————————————————
Try
‘Determine the current Datetime and convert it to the specified string format
Dim dateTimeInfo As DateTime = System.DateTime.Now
Dim dateString As String = dateTimeInfo.ToString(dateFormat)
‘Locate the index of the file extension
Dim extPosition As Integer
extPosition = destFile.LastIndexOf(“.”)
‘Insert a dash and the formatted date string into the destination file name
destFile = destFile.Substring(0, extPosition) & “-” & dateString & destFile.Substring(extPosition)
Try
‘Move the file
IO.File.Move(sourceFile, destFile)
Catch ex As Exception
‘If an error occurred, alert the user and return false to abort the map
System.Windows.Forms.MessageBox.Show(“An error occured while attempting to move the file: ” & ex.Message, “File Error”)
Return False
End Try
Catch ex As Exception
‘If an error occurred, alert the user
Messagebox.Show(ex.Message, “MoveFileAddDate Script Error”)
End Try
Return True
‘– End of Script
The first section of the script is comments that list the script name, date, purpose, etc. The single tick mark ‘ declares that anything following it on that particular line is just a comment and not more code. As you can see, almost every major line has a comment just above or below it explaining what the code is doing.
With these sample scripts, we have tried to place all the variables or configuration options in the same location in the script to make it easier for users. Just under the header comments at the beginning of the script there is a Configuration section which contains anything that would need to be modified to get the script to function correctly.
In this case, there are three variables that need to be configured. The first one is the dateFormat variable. This variable is going to determine what is appended to the file name when the script moves it. By default, dateFormat = “MMddyy”. So if our filename was “test.csv” and today’s date was 12/31/2009, then when the script moved the file the new name would be “test-123109.csv”. dateFormat can be set to other values as well. If you wanted the time, you could say dateFormat = “MMddyy-hhmm”. This would give a result of “test-123109-0943.csv” if it was 9:43 in the morning when the file was moved. The only restriction is that you shouldn’t put restricted characters into the string (ie periods or slashes) as that can mess up the file name.
The next two variables that can be configured are the sourceFile and destFile variables. The sourFile variable should be set equal to the path where the file you are going to move is located. The destFile variable should be set to where you want the file moved. You can also change the file name in the destFile if you desire. Let’s say my “test.csv” file was sitting on my desktop and I want it moved to a folder name “Completed Integrations” that is also on my desktop. I want to change the file name a bit to reflect that it was successful, as well. My configuration variables would be as follows:
dateFormat = “MMddyyyy”
sourceFile = “C:Documents and SettingsAdministratorDesktoptest.csv”
destFile = “C:Documents and SettingsAdministratorDesktopCompleted Integrationstest-integrated.csv”
The file would be moved to the Completed Integrations folder and would be named “test-integrated-12312009.csv” when the script ran.
Now, how can you use this effectively? Here’s a scenario where this script could complement other SmartConnect features. Let’s say that we have a file that is generated daily and placed in some folder on our machine. We also have a scheduled map that runs once a day that uses that file as the data source and then integrates it data into GP. Currently, somebody manually moves the file each night so that the new one can be placed in that folder.
This script could be set up in two places on the map so that the file is automatically moved to a specified folder based on whether the map succeeded or failed. The script could be put in the Tasks -> Map -> After -> Success location which would move the file to a successful integration folder. It could also be placed in the Tasks -> Map -> After -> Fail location to move the file to a failed integration folder. This would help streamline your integrations by adding another layer of automation to your system.