Create export files from SQL code via IPA or crystal and LBI?

We are not IPA yet, still PFI, but we have many flows that have an SQL node to pull the desired data and write it to a file in the desired format (using the File Append node). All of our interfaces are done this way.

Joan this is certainly a viable approach. I work with a number of clients who use this to extract and interface data. Some caveats apply. For instance, using file node will not scale if IPA not on same server as LSF. You need to use message builder instead. Also beware of what you're querying and how much you're trying to do. You don't want to query for 10 million rows and try to iterate over that in IPA.

The reason for my post is because we well be switching from a Unix platform (and kron jobs) w an Oracle SQL DB to a Microsoft platform w/ MSSQL DB. We have hundreds of scripts (.ksh) that will need to be replaced during our upgrade. So while I'm implementing a couple of new processes I'm trying to determine the best way to handle our file transfers from Lawson to another system. Most of the existing ones have some type of script that runs to manipulate or move the file, so if we can determine our 'best practice' for ftp'g our files and start migrate some of the existing scripts prior to the upgrade that would be a good thing. We have several different applications that could be used to do this and after further discussion with our dba, we've decided that IPA is not the best tool, available to us, to use for this issue.
Thanks for your input.

Joan, what about using the Cygwin functionality that is installed with Lawson. Granted, you'll need to install far more than what is required to get Lawson itself to work, it should do most anything you need that is inside a kph script.

Another option would be to rewrite in Powershell or Perl.

Hi Kwane - I know Powershell and Perl have been discussed for the more complicated scripts.  I'll have to ask my sys admin about cygwin since I don't know what that is. We have some planning meetings scheduled in the near future. I'm sure the simple ftp scripts will be handled one way, the scripts that load information into lawson with be replaced with IPA flows and the other more complicated scripts...will depend on what they do.  Needless to say we have some fun ahead of us ;-).  Thanks for your input.

Cyqwin is an open source project that aims to provide a number of standard Unix tools and a mostly POSIX compliant environment to Windows ( https://www.cygwin.com/).

Properly set up it provides a large portion of the functionality that you would have in a Unix box, on a Windows box. It's usually easier to transition a script from a 'proper' Unix environment to a Cygwin environment than it is to move straight from Unix to a purely Windows based solution such as Powershell or DOS batches, as you are still using the same tools and commands, you just have to tweak things a bit to work around Windows based wonkiness.

There are some downsides to Cygwin, but most of them are almost entirely outside the scope of what I would perceive the normal Lawson/Infor sort of environment to be worried about.

Since this will be Windows and SQL Server, I would simply convert the extract script to a batch file. Use SQLCMD to select the view and output it to a file. You can then just copy the file to wherever. You can then have the the script write a line to some log so that you can check to make sure it ran or you could also use some freeware program like bmail to email you an alert that the batch file ran. After you have set up the script just have your network people schedule it in the Windows Scheduler.

We do this with several of our extract scripts. It is simple and easily maintained.

We are a Windows shop too and what Ari suggested is pretty much what we do as well. We do all of our ftp'ing (not just Lawson but all apps) from a dedicated ftp server. For Lawson, all of the file creation and manipulation that is necessary for any of our Lawson jobs is presently accomplished through a series of batch & vbscript files, which we are in the process of converting over to PowerShell.

These scripts use the SQLCMD for the Lawson queries where appropriate. Some of these are triggered by a scheduled task. However many others are fired off by the creation of a designated trigger file (like the output file from a standard Lawson job for example). For this process, we have a script that we refer to as our "Folder Monitor" script, which runs on the Lawson server and monitors all identified directories for the presence of these trigger files and calls/runs the designated batch/vbs/PS script each time the trigger file is found to exist. Incorporated into each batch/vbs/PS script that we write that gets run from this process is a notification procedure, which sends the appropriate person(s) notifications of the job having either run or failed.

Then, there is also a second folder monitor script process that runs on the ftp server, generating additional notifications to relevant parties, as to whether or not the associated/subsequent ftp job ran successfully or failed.

Well, one thing is, some fields in Lawson are encrypted, such as SSN. So, you would need IPA to fetch the actual data via a LandmarkTransaction or S3Query.
The MessageBuilder would be used in a loop as long as there is no parsing necessary as you're reading records. If so, this won't work. You'd have to use an Assign node to concatenate to a variable with column and row delimiters. I do this currently in a few flows. If you don't need IPA for the above reasons, then, for instance, I have a custom SQL Database that lives on the same sql instance as landmark and I use views to fetch the data. From there, you could BCP or Bulk executions to output.
If the former, then IPA has FTP and a SYSCMD node to execute system commands to move files.

Joan,

I have created multiple files and saved them to folders many times. Email me if you would like to chat about this.

Firegeek21@gmail.com