Trying to figure out a sensible way to achieve this.
I receive a data file which is a straight structured fixed length file with cr/lf delimters. Something like:
aaaaaaaaaaaBBBBBBBBBBB123.45
My objective is simply to import this into rows in a table.
This needs to be within a transaction, and can be part of a .NET process. It's sql server 2008.
Ideally I would like to pass an array of "records" to a stored procedure, because further processing is required on them.
It seems to me that I could achieve this by:-
1. Create a TYPE as a table in SQL server
2. Read the file in .NET and create a DataTable from it
3. Pass said datatable and any other parameters to the stored procedure
4. In the stored procedure, create a transaction, iterate the table via a cursor, do stuff with the data, end the transaction
Is this the usual sort of way or should I be looking at something different ??
Another hiccup.
The file should only be able to be processed once and needs to be deleted, but only in the success case.
Not sure how best to achieve this because it could fail after the sp completes and before the file gets deleted.
My thought is to also have a table containing the details of files that have been processed, then when I find a file if it is in there I know it has already been done and can ignore it. This table could be updated in the transaction in the stored procedure.
Again a bit klunky, so any other suggestions are welcome.
I receive a data file which is a straight structured fixed length file with cr/lf delimters. Something like:
aaaaaaaaaaaBBBBBBBBBBB123.45
My objective is simply to import this into rows in a table.
This needs to be within a transaction, and can be part of a .NET process. It's sql server 2008.
Ideally I would like to pass an array of "records" to a stored procedure, because further processing is required on them.
It seems to me that I could achieve this by:-
1. Create a TYPE as a table in SQL server
2. Read the file in .NET and create a DataTable from it
3. Pass said datatable and any other parameters to the stored procedure
4. In the stored procedure, create a transaction, iterate the table via a cursor, do stuff with the data, end the transaction
Is this the usual sort of way or should I be looking at something different ??
Another hiccup.
The file should only be able to be processed once and needs to be deleted, but only in the success case.
Not sure how best to achieve this because it could fail after the sp completes and before the file gets deleted.
My thought is to also have a table containing the details of files that have been processed, then when I find a file if it is in there I know it has already been done and can ignore it. This table could be updated in the transaction in the stored procedure.
Again a bit klunky, so any other suggestions are welcome.
Comment