{"id":3496,"date":"2023-04-12T15:33:27","date_gmt":"2023-04-12T15:33:27","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/integrating-data-from-ftp-sites\/"},"modified":"2026-01-20T16:13:56","modified_gmt":"2026-01-20T16:13:56","slug":"integrating-data-from-ftp-sites","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/integrating-data-from-ftp-sites\/","title":{"rendered":"How Do I Integrate Data from FTP Sites into IBM Planning Analytics?"},"content":{"rendered":"

In other articles I\u2019ve discussed the process of integrating data from various sources into IBM Planning Analytics. This time, I want to offer a simple strategy for automating the inflow of data \u201cdropped onto\u201d a F<\/u>ile T<\/u>ransfer P<\/u>rotocol (or FTP) site. Since the File Transfer Protocol<\/em> is a standard communication protocol commonly used for the transfer of data files from a source to a client, sooner or later you will come across a scenario where files may be sitting in a secured FTP folder waiting for you to download and consume.<\/p>\n

Validating \u201cthe Drop\u201d<\/strong><\/p>\n

I always begin by using an FTP Client tool such as FileZilla<\/a> to \u201cvalidate the drop site\u201d.\u00a0 With FileZilla, you enter the IP or Computer name of the host site, the required credentials and port number and click connect. This allows you to validate the information provided: host URL, authentication credentials (Username and Password) as well as the host\u2019s folder structure. You should go through the exercise of uploading and downloading a file from the location to make sure that you can access the site successfully, and files can be transferred to and from (the site) without affecting content or encountering performance degradation.<\/p>\n

Automating with PowerShell<\/strong><\/p>\n

Since there currently is little support for FTP automation offered directly in Planning Analytics, we can use PowerShell. PowerShell is a task automation and configuration management<\/em> program from Microsoft, consisting of a command-line shell and a associated scripting language. PowerShell is an obvious choice for automating the process of downloading files from an FTP site to a place where Planning Analytics can consume them and PowerShell can also perform any tasks required to move, reformat or otherwise prepare the data for loading, including supporting PGP encryption.<\/p>\n

If we commit to using PowerShell as a solution, than the Windows PowerShell Integrated Scripting Environment<\/strong><\/a> (ISE)<\/strong> (a host application for Windows PowerShell) is my<\/em> scripting tool of choice. In this ISE, you can run commands and write, test, and debug scripts in a single Windows-based GUI (graphic user interface). To access the ISE, you click Start, select Windows PowerShell, and then click Windows PowerShell ISE<\/em> (alternately, you can type powershell_ise.exe in any command shell or in the Run box).<\/p>\n

Get Busy Scripting<\/strong><\/p>\n

We NEVER want to \u201chard code\u201d anything in any program or script, therefore, we\u2019ll build a script with the ability to accept runtime parameters<\/strong>. In this exercise I am going to store a username and password in a secure global cube (within Planning Analytics) and then pass those values into the script at runtime. In addition, I want to use this script to download any file (in a predetermine location) so I\u2019ll add a 3rd<\/sup> parameter for the filename that I want to download. PowerShell parameters are always enclosed in a param block defined with the param keyword, followed by opening and closing parentheses, so the top of our script will look like this:<\/p>\n

 <\/p>\n

param(<\/p>\n

[Parameter()]<\/p>\n

[string]$username,<\/p>\n

[string]$password,<\/p>\n

[string]$filename<\/p>\n

)<\/p>\n

Cheating a Bit<\/strong><\/p>\n

I said never hard code<\/em>, but for this exercise I AM going to go against that and define 2 \u201chardcoded variables\u201d (hardcoded because I have a specific source\u00a0 and destination and \u201cvariable\u201d because I will use the filename parameter value to \u201cqualify\u201d it). \u00a0$url is the source (the URL and filename we want to download) and $dest is the target (where the file will be downloaded to):<\/p>\n

# — URL and Destination<\/p>\n

$url = “https:\/\/jims.solidftp.scape.com\/in\/” + $filename<\/p>\n

$dest = “C:jimsftpdownloadsTestFiles” + $filename<\/p>\n

Let\u2019s get to the Downloading!<\/strong><\/p>\n

With PowerShell, it\u2019s all about using its \u201clibrary\u201d of cmdlets. A cmdlet<\/strong> is a lightweight command that is used in the PowerShell environment (almost like a \u201cmini\u201d script or program invoked by a single line of code). The PowerShell runtime invokes these cmdlets \u201cwithin the context\u201d of automation scripts that are provided at the command line. The PowerShell runtime also invokes them programmatically through PowerShell APIs.<\/p>\n

\u00a0<\/strong><\/p>\n

Windows PowerShell doesn’t have any cmdlets specifically designed to download Internet data, however, PowerShell can be object oriented, so we can use the New-Object<\/strong> cmdlet to create an object using the System.Net.WebClient<\/strong> class and expose common methods for sending and receiving data from any local, intranet, or Internet resource identified by a URI:<\/p>\n

 <\/p>\n

# — create an object to download file<\/p>\n

$web = New-Object System.Net.WebClient<\/p>\n

 <\/p>\n

Note that in the script above the object is \u201cstored\u201d in the variable named $web. The next step is to set the objects Credentials property<\/strong> with the network credentials that will be sent to the host and used to authenticate our download request:<\/p>\n

 <\/p>\n

$web.Credentials = new-object System.Net.NetworkCredential($username, $password)<\/p>\n

 <\/p>\n

Finally, DownloadFile<\/strong> is the method that will invoke the actual process of downloading the file that we specified:<\/p>\n

 <\/p>\n

$web.DownloadFile($url, $dest)<\/p>\n

Our \u201ccomplete\u201d script might look like this:<\/p>\n

\"\"<\/p>\n

Testing<\/strong><\/p>\n

It is always important to take things \u201cin steps\u201d, meaning, first<\/em> I used FileZilla to confirm that the URL and network credentials were correct and working. Now I will manually run the PowerShell script we just created and see if it can successfully download a file. To do this we can type into the console the following:<\/p>\n

.myposwerd.ps1 -username apptm1jim -password tH8Bg6ehtoNyfLD9vOV -filename TM1_Integration_Report_Output.csv<\/p>\n

This should execute our script passing it the 3 parameters we described earlier:-username, -password and -filename (a file named \u201cTM1_Integration_Report_Output.csv\u201d). It is assumed that the file exists on the FTP site and in the \u201c\/in\/\u201d folder.<\/p>\n

By default, PowerShell scripts are \u201cunsigned\u201d meaning they do not automatically have a digital signature<\/strong>. A \u201cdigital signature\u201d is a type of electronic signature that’s secure and can be authenticated, indicating that the script can be run on a system, safely. Since we are not publishing our script to multiple consumers, we can avoid the process of having to create a digital signature by setting a specific execution policy<\/strong> on the specific system that the script will be executed (running as an unsigned script). To run unsigned scripts, you must change the execution policy using the Set-ExecutionPolicy<\/strong> cmdlet. You may need to alter the specific execution policy based upon a particular client\u2019s security program or plan. Start out by running Get- ExecutionPolicy <\/strong>to see the current policies and scopes:<\/p>\n

\"\"<\/p>\n

Adding PGP Encryption<\/strong><\/p>\n

Along with securing the FTP folder<\/em> (using a User and Password), the files coming and going in the folder should be encrypted. Pretty Good Privacy<\/strong> or \u201cPGP\u201d is an encryption program that provides cryptographic privacy and authentication for data communication. PGP is used for signing, encrypting, and decrypting texts, e-mails, files, directories, and whole disk partitions and to increase the security of e-mail communications. PGP adds a second layer of security to a solution and thankfully, PowerShell makes dealing with PGP quite easy.<\/p>\n

 <\/p>\n

Start with a Key<\/strong><\/p>\n

To implement PGP, a PGP key must be generated which you will use to sign and encrypt files. When you create a PGP key, a keypair<\/em> (consisting of a public key and a private key) is generated. You can share the public key with anyone who wishes to send you encrypted files, but the private key must be known only to you and used to decrypt received files. You can use tools such as GoAnywhere Open PGP studio<\/a> to create a PGP keypair or, like I did, use PowerShell commands available it modules such as PSPGP<\/a>, which is a PowerShell module that provides PGP functionality in PowerShell allowing encryption and decryption of files\/folders and strings using PG.<\/p>\n

 <\/p>\n

Note: The PowerShell system is already available within the Windows operating system, and you probably already have many modules available (if you are not sure, at the PowerShell prompt, type Get-Module –<\/strong>ListAvailable<\/strong> to see which modules are available for you to use). If a particular module is not available (such as PSPGP), you can install it by using the Get-InstalledModule<\/strong> cmdlet.<\/p>\n

 <\/p>\n

You can use the New-PGPKey<\/strong> command in a PowerShell script to generate a PGP Key Pair (pick your own UserName and Password):<\/p>\n

 <\/p>\n

# — create a new PGP key<\/p>\n

New-PGPKey -FilePathPublic $PSScriptRootKeysPublicPGP.asc -FilePathPrivate $PSScriptRootKeysPrivatePGP.asc -UserName ’69przemyslaw.klys’ -Password ’69ZielonaMila9!’<\/p>\n

 <\/p>\n

Below is the key pair generated by the above script:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Decrypting with PGP<\/strong><\/p>\n

Once you have generated your key pair (and shared the Public key) you can use the Unprotect-PGP cmdlet to decrypt files. The following script demonstrates the decryption of a file (Encoded \u2013 is where the encrypted file resides and Decoded – is where the decrypted file will be written):<\/p>\n

 <\/p>\n

# — use PGP key to decrypt the file<\/p>\n

Unprotect-PGP -FilePathPrivate $PSScriptRootKeysPrivatePGP.asc -Password ’69ZielonaMila9!’ -FolderPath $PSScriptRootEncoded -OutputFolderPath $PSScriptRootDecoded<\/p>\n

 <\/p>\n

A simple test result is shown below – before decryption:<\/p>\n

\"\"<\/p>\n

And after<\/em> decryption:<\/p>\n

\"\"<\/p>\n

TurboIntegrator<\/strong><\/p>\n

The last step (in this exercise) is to use a TurboIntegrator process to initiate the PowerShell script we wrote. If you are comfortable with Planning Analytics, it should make sense and be straight forward.<\/p>\n

 <\/p>\n

The key points are:<\/p>\n