Solved

Biztalk - is this a possible platform for standardizing file imports and business rules?

Posted on 2014-12-17
2
217 Views
Last Modified: 2015-01-06
Hello all,

I am looking at some possible enterprise based solutions for handling file importing and trying to standardize one place to setup file imports and business logic / validations on the import and processing.   I know this is a very broad point and SSIS has been my ETL tool of choice when doing any of this except for any deep business rules.   Looking to see does BizTalk have better ways to handle that.  Some examples would be:

- Standard File Import file type filtering
- Checking for things such as file header etc. is there
- Validating data types possibly on columns prior to importing to a SQL database table for example.
- Applying business rules and then flagging things that need to be changed in the file for a visual the end user can change a field for example.

Not sure any one tool is feasible to do all of these things but looking even for the 90/10 rule that much of it can be done possibly.   But the idea of importing files into one portal where then the user can modify things and apply and retry is what I am looking for.
0
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 40515776
BizTalk definitely has the ability to handle that, but be careful that you're not chartering a bus to go pick up fast food, as BizTalk is a bit of a beast.

When it's configured and working properly, it requires very little care and is incredibly stable, but we've found that deployments and code updates are unnecessarily complicated. However, it does excel at converting every conceivable format into a central message exchange and then either sending that message on to another system or persisting it somewhere for you (like an MSSQL database). That's actually how we use BizTalk. It does also support some pretty complex transformations, transactional integrity, and a number of other features, but we're just using it to move files around to and from vendors, persist them when necessary, do some basic processing, and then send out summary emails.

If you're already using SSIS for ETL, why not look into doing custom C# coding there instead of BizTalk (which also uses C#, but could use any .NET language like VB.NET too)? You could do a custom scripting task if you had to parse an incoming file into some specific format, interpret XML, or do any other massaging of data, and then SSIS does an excellent job of moving it to where it needs to go. While BizTalk can be configured to fetch files from any location and send them pretty much anywhere, SSIS can handle many of the same locations (though BizTalk may be a bit more extensible here).

In the end, BizTalk definitely has more functionality, but it's also significantly more complex and is built handle very intensive communications between tiers in near-real-time - if you're just importing and parsing files and time isn't super-critical (as in they can be a few minutes late), I think you're better off leveraging SSIS and just extending the functionality you need using some .NET coding.
0
 

Author Closing Comment

by:sbornstein2
ID: 40533457
sorry for the late accept here.  great information thank you
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I find this data? 3 25
Linked Server - SP with Param to VIew 7 21
Related to SQL Query 5 20
add stored proc on publlication 4 20
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question