December 7, 2008 at 7:16 pm
Hi All,
The process requires reading files that is being dumped into a certain directory called the "Pending Directory", the "unknown module" must be able to read from the pending directory from time to time if there are other files being dumped.
The file that is being dumped into the pending directory is called a "Batch File" which is a text file with a comma separated data inside it, format inside a batch file is similar to this:
FORMAT:
BATCH_ID,TABLE_ID,ROWID,MODE,COLUMN_ENTRY,COLUMN_ENTRY,COLUMN_ENTRY,.....
----------------------------------------------------------------------------------
BATCH_ID = There is one(1) batch_id per batch file, and there are many tables per batch file
TABLE_ID = The ID of the table on where to add, modify, or delete the data.
ROWID = Typically the row identification per data
MODE = This is the action that will take place for a certain data, its either Add(A),Modify(M), or Delete(D).
COLUMN_ENTRY = certain data for a specific column in a table.
e.g.
1,22,1,A,Column 1 Entry,Column 2 Entry,....
1,22,2,D,Column 1 Entry,Column 2 Entry,....
1,22,3,M,Column 1 Entry,Column 2 Entry,....
1,22,4,M,Column 1 Entry,Column 2 Entry,....
1,22,5,A,Column 1 Entry,Column 2 Entry,....
1,25,1,A,Column 1 Entry,Column 2 Entry,....
1,25,2,A,Column 1 Entry,Column 2 Entry,....
1,25,3,M,Column 1 Entry,Column 2 Entry,....
1,25,4,M,Column 1 Entry,Column 2 Entry,....
1,25,5,M,Column 1 Entry,Column 2 Entry,....
I need to design a process with the following requirements:
1.) A module or something that will timely read Batch files from the Pending directory
2.) Convert the data into an XML format and validate it using XML schema
3.) Log if there are erroneous data based from the validation above.
3.) Insert the data into a Maintenance Database
4.) After inserting the data, check the constraint, log if error or success.
5.) All success data shall be inserted to Final Database.
Right now, I am looking into the following services and technology (SSIS,DTS, and Bulk Insert) to see if it fits with the following requirements, I just don't know if I am on the right direction. If someone can tell me a concrete strategy or design on how to deal with this kind of requirements, then that would be great.
FYI. I am using SQL Server 2005 Express Edition.
Thank so much for your time.
December 7, 2008 at 8:40 pm
This is a duplicate post.
Please do not cross-post. All replies should be directed to: http://www.sqlservercentral.com/Forums/Topic615332-148-1.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply