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?