Importing a data from a flat file to SQL server.

[LondonManc]

You can actuallly become a guru in SSIS ? I thought it was the tulip you gave to the junior

That was DTS. SSIS got far more functional

[dx4100]

Indeed, one becomes a SSISguru

You can actuallly become a guru in SSIS ? I thought it was the tulip you gave to the junior

[DimPrawn]

One does not simply play with SSIS

Indeed, one becomes a SSISguru

[LondonManc]

One does not simply play with SSIS

[ASB]

Cheers. I shall have a play.

[LondonManc]

Use SSIS - exactly what it is built for.

[DimPrawn]

I'd be tempted to create a staging table, that mimics the structure of the file, and use SSIS to create a package to import the data.

Then call a sproc to process the data in the staging table further.

How to import a fixed width flat file into database using SSIS? - Stack Overflow

Don't know if this helps?

[ASB]

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.