- This topic has 2 replies, 1 voice, and was last updated 9 years, 2 months ago by
.
-
Posts
-
-
Hi all,
I am using biztalk 2009 and sql server 2008, vs2008.
any information is greatly appreciated – I am not able to find any tutorials or articles on how and where to start
I need help on figuring out how to do the following. I have csv file which I am able to map to sql server tables using updategrams and I am successful in importing the data to respective fields in multiple tables. (If the data in csv file is clean and straightforward then everything is smooth – but the system I am developing is a bit complex)
now I need to do validation on the data that is coming in
1) I need to retreive the csv file name into a attribute in flat file schema and retreive a part of the filename and parse it into the sql server table field.
2) I need to check if the data coming from csv file already exists (if yes then dont insert that Customer record and only update that customer’s information)
3) validation for datatypes , length,spaces, -this all I cannot do in the map through functoids (In the map I can only check for schema mapping – if the schema matches or not -how can i do this)
4) where and how to do data validation in biztalk – so that correct data is imported to sql server (How to seperate schema validation from data validation in biztalk)
thanks
-
If you are using BT2009 and SQL2008 you should be using the new WCF SQL adapter. The old SQL adapter has been deprecated and will not exist in BizTalk vNext.
With with this new adapter you can use a table parameter with a stored procedure and the new Merge statement.
CREATE TYPE YourCSVFileFormat AS TABLE
(Id INT
,Column1VARCHAR(50)
, Column2 INT
,etc,etc );CREATE PROCEDURE usp_InsertProductionLocation
@CSV YourCSVFileFormat READONLY
AS
MERGE DbTable AS target
USING @CSV AS source
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET Column1= source.Column1, Coumn2 = source.Column2
WHEN NOT MATCHED THEN
INSERT (Column1, Column2)
VALUES (source.Column1, source.Column2);By creating these SQL objects you can use the Add Generated Item feature in VS2008 to generate the table schema in Biztalk. Then simply map your flat file schema to your table schema
1. To put the filename into the SQL table, you can use a map and the ContextAccessor functoid (http://www.codeplex.com/ContextAccessor). The property you need to map is FILE.ReceivedFileName.
2. This is a feature of the Merge statement.
3. Datatype validation you can do by extending the schema. Select a particular field node and in properties window set Derived By to Restriction. Scroll down to the Restriction properties where you can set Min and/or Max length, even a regex pattern. You can use the Xml Validation pipeline component to validate. Remember you lose whitespace in Xml so any checking that involves spaces will not work.
4. Data validation is somewhat trickier, what sort of data validation are you expecting. I have used a custom Xslt map with some .NET classes that cache various data items from a database or config file.
One of the key question is how do you want to handle errors, discard current record and continue or abort and roll back or records?
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.