CSV file to SQL table

Don’t know an example, but here are some steps.

1) Design your flat file schema. Use right-click \”Validate Instance\” until it works with your csv file.

2) You can optionally map your data to a regular XML schema. You will use this XMl in the next step. (If you want to build an simple csv-file-in/xml-file-out orchestration at this point you could. Get something small and easy working, then add to it step by step.)

3) Most likely, your CSV file will contain many records, not just one, correct? You will need to write a stored proc that uses OpenXML. See this blog: http://nealwalters.blogspot.com/2005_12_01_nealwalters_archive.html .

NOTE: There might be an another alternative here, but I have not tried it. You could try to build the updategram insert statements directly, but where I have worked, we always go through stored procs.

4) Run the SQL Adapter to build a schema that represents the input/outputs of your stored proc. Your input parm to the stored proc will be one parm, something like \”xmlin\”. I always like returning a least a success/failure flag from the stored proc so that I can call it syncronously with request/response. In the schema, promote or distinguish your parm to make it easy to set below.

5) Create an orchestration to receive the CSV file, map it to xml, use construct/message assignment shape to set parm, then call stored procedure (Send/Receive from a request/response port).

Neal Walters
http://Biztalk-Training.com

P.S. My training videos include an entire CD on the SQL adapter, and also some videos on flat file schemas.