Thanks Jeff,

I looked at your spreadsheet. Are you talking about a small application there

a teacher wants to register scores for the students?

During a period more students, subjects and months...

"The real extraction that I need is batch oriented and did move to a new database on a new server and the result was about the same on the amount...

AtoZ67

"This particular Linked Server is AS400 but also have other linked servers which are SQL Server and queries slow there as well."

What kind of internal net do you use? What...

Do you mean Agresso from the Dutch Company UNIT4?

Agresso is frequently used in Sweden.

Agresso has many modules. If you start with the general ledger

there are only about four tables which...

"Examples: End of line format changing (CR vs CRLF), "NULL" appearing in numeric columns, delimiters changing (e.g. normally a pipe, but occasionally appear as commas instead)." If with Changes within...

"it's a file that will be supplied multiple times a day so it's not feasible to convert it every time."

Why?

You have to give us more background information.

"and...

Take a look at PowerPivot .

"I can see no way of pushing the new/changed records back to SQL Server from within Excel itself. Does this bit need to be done programmatically with e.g. VBA?"

Yes there...

Look at the Filter function

🙂

I forgott to say that my script is VB.net

This is an alternative solution

processes = Process.GetProcessesByName("Excel")

For Each proc In processes

...

Randy

You are not the only one having the described problem "by design" from Microsoft.

One work around is to add a small scriptpart which kills the process after quit.

Process.GetCurrentProcess()

...

If you know VBA in Excel write the code to open the database and insert the data from Excel in a table. Use Google to find examples.

I never use SSIS...

You don't tell us how big the file is.

You might not be able to store it in the internal memory and read it reversed?

Anyhow you can read the file...

Have you tryed to save the excel file as a TAB separated textfile

and use SSIS to import the text file?

If some rows then are missing the problem is in the...

A more safe way is to save the Excel file as a TAB separeted textfile

(Make a small macro activated when the Excelfile is opened or closed).

Import that textfile with...