EDI format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

EDI format

Author

Topic

jadoger
Starting Member

13 Posts

Posted - 2007-10-02 : 07:40:52

how to use SSIS to produce file in EDI format ? any thoughts?

thanks

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 08:23:33

Is "EDI Format" something like:

SECTION A
Row1 Col1 Col2 Col3 Col4 ...
Row2 Col1 Col2 Col3 Col4 ...
SECTION B
...

because if so I do have some suggestions.

Kristen

jadoger
Starting Member

13 Posts

Posted - 2007-10-02 : 09:04:15

Yes it is

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 09:45:42

What we have is a little VBScript routine that calls a "Given me some data" Sproc.

We use the same routine for both EDI and API interfaces (so some of my description below may only be applicable to API interfaces; brew your own at will!)

The "give me some data" Sproc takes a SessionID parameter. The VBScript cannot give this on the first call, but gets it as a result from the first instantiation and then uses it on subsequent calls.

The "give me some data" Sproc uses a "session" table to decide where it has got to. Something like:

1) Get all new customers
2) Get all changed customer
3) Get all new orders.

FWIW: We have a limit on how many new customers / orders per batch (e.g. on a very busy day the EDI stuff may not "catch its tail" until 2am), and we use the Session Table to control how-much-data, and which-job-next.

The "give me some data" Sproc checks which "section" it is in, and how many times it has been called (if necessary), and then calls the appropriate Sproc - e.g. "GetNewCustomerData_SProc"

This returns data with a special initial ResultSet, and sometimes some special columns [e.g. to provide a "Hint" to the VBScript App]:


SELECT	[Object] = 'EDI',	-- Use EDI method, NOT API method
	[Method] = 'EDI',
	[UpdateSP] = 'xx_SP_Order_ExportUpdate',	-- SP to call with results
	[ERROR] = 'EBAD',				-- Status for ERROR
	[SUCCESS] = 'EOK',				-- Status for OK
	[Session_ID] = @Session_ID,
	[PK] = @Order_ID				-- Primary Key (for callback)

(The [PK] is a concatenation of all multiple-part PK keys; it is used by the CallBack SProc to update the relevant record with the Success/Fail outcome)

The VBScript App records this and uses it to (generically) decide what to do next.

Then the Sproc outputs some data like:


SELECT 'ORDERHEAD', 
	[orderref] = CONVERT(varchar(20), Order_ID),
	[orderdate] = RIGHT('0000' + CONVERT(varchar(4), datepart(year, Order_Date)), 4)
			+ RIGHT('00' + CONVERT(varchar(2), datepart(month, Order_Date)), 2)
			+ RIGHT('00' + CONVERT(varchar(2), datepart(day, Order_Date)), 2),
	[ordertime] = RIGHT('00' + CONVERT(varchar(2), datepart(hour, Order_Date)), 2)
			+ ':' + RIGHT('00' + CONVERT(varchar(2), datepart(minute, Order_Date)), 2)
			+ ':' + RIGHT('00' + CONVERT(varchar(2), datepart(second, Order_Date)), 2),
	'',
	'220',	-- Type of order 220=Purchase
	[ORDERDESC] = '',
	[FLGN] = '',
	[FLVN] = '',
	[FLDATE] = '',
	[FLTIME] = '',
	...
FROM ...

and the VBScript App just outputs the data it is given, in column order, delimiting with comma, or whatever the EDI requirement is. (We have various Output Styles that we have encountered over the years

(Comma delimited, Fixed length; one file per record, multiple records per file; etc.), and the Output Style required is signalled to the VBScript App by a value in the first "Config ResultSet")

Then the SProc outputs the next "section" of data, maybe Delivery details, etc., and then OrderItems and so on:


SELECT 'ORDERLINE', 
	[orderref] = CONVERT(varchar(20), Order_ID),
	[OrderLine] = Order_Item,
	...
FROM ...

and again the VBScript chucks out the data it is given.

Eventually the Sproc ends, the VBScript then uses the [UpdateSP] from the first ResultSet to signal back if all was OK. This is more relevant to an API call, where there is likely to also be an "OtherSystemID" to signal back too, but errors detected in the data, disk full, remote system offline, whatever, can signal back a failure.

Then the VBScript calls the "give me some data" Sproc and either gets the next order, or a ResultSet which says "All work completed, STOP!"

Sprocs which send data to be passed to an API send a ResultSet with column names: [Object], [Method], and then a list of columns - with the Column Names matching the Names of the API objects, and the VBscript instantiates the Object, sets up each Property (i.e. matching each Column Name) and then calls the Method to "post" the data.

Our thinking behind this was to have a 100% generic EDI / API generator, and to control the names of columns / sections / API Objects/Properties/Methods, and the means by which Success/Fail can be signalled to the database, solely within the SProc.

So if the EDI data has to change we alter the Sproc, run it in isolation to check that the data it is generating is "as expected", then put it live.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources