Thursday, February 11, 2016

Old School (cloudfree) File Integration

It's already 2016, time flies when you been too busy for blogging :-)

Nevertheless, it is still amazing that integration between to systems is still based on old school text files like (in)famous CSV file.

Of course it still works and once configured  right you have solid integration, but I would still prefer xml file exchange or even better use one of the available adapter for your fav integration tool.

In case you have some need for this ol' skool integration you can do this of course with Scribe Software it's possible with either online or onpremise.

Although the Cloud is hot and we have still have freedom of choice ... yes, you can choose between  cloud and on-premise.. cloud, but if you want it all you have to go cloud and you know cloud is the best. Hmmm, still many customer for MS CRM still have on premise or partner hosted environment....

So, in this short example we still go for on-premise e.g. Scribe Insight / CRM on premise :)

A common scenario that you will need to monitor fileshare and when new CSV file arrive, we have to process it toward Dynamics CRM and either delete or archive the processed file.

First step would be to create ol' skool ODBC 32bit and
Create ODBC 32bit!!

Next step is to define data source name, database directory (windows location) etc, the next step would be to hit "advanced" tab and click define to specify every row. This is important for assigning the data type.

ODBC - Define Table

This will be your data source, of course next step is too create your scribe dts file, but I am sure you would be able to select source (odbc), target (CRM) and create your own mapping.

More interesting are the settings for the integration process and I always forget this, so I have to open and dust off the archive...

To be in style also some screenshots from older Insight version ;-)

In the first step of configuring you
the basics like processe name, collaboration, DTS file and also DTS Parameters where most important is '/SD=%FileName'.

In the second step you can define your pre- and post operation.For the first step you could move incoming file to dedicated directory or might even code to retrieve file from ftp location.

In post command you usually place code to archive you file.Depending on which functionality you use either custom bat file or standard scribe archive solution you can add parameter like '%FileName'

In the third step you location where you file will be placed e.g. location where you put your file for processing. You can use wild card like '*.csv' or in screenshot '*.xml'

Further you can define poll time, run it once of everytime new file arrives and locking file, delete after processing. This would depends on your design.

Last two steps
The last two step are basic, so that shouldn't be a problem.

Bat File
One of the other challenge is to get the right file name in your bat file. The trick is to config the parameter in step one. In your bat file you can refer to it with '%1'. A codesnippit including creation of log file should be something like this:

"move /-y "%1"  "D:\Example\dir1"   >> "D:\Example\dir1\Cmdlog.txt" 2>&1"

Furthermore your scribe service account should have the correct privileges to read & write.

Happy Ol' School integrating!

No comments: