Excel to sql server convertor using SQL server express edition

Question

Hi,

I need a way to convert from Excel file to the popular db formats (SQL server is the most important to me at the moment).
Visual studio and it's SQL server, came with  DTSWizard ( Import and Export Data (32-bit) ), which is lacking the following feature I need:

1) If the row has an invalid value, it should skip it, write it to a log, and continue exporting the other rows.

The DTS lacks the proper log mechanism, and every option I tried to make it write the rest of the rows on error have gone down the drain...

DB isn't my expertise, but from goggling around I saw that maybe it's possible by creating something called "SSIS package" - Am I right?

My problem is that I must use  a freeware tool - meaning the tools built in  SQL SERVER EXPRESS or some other program (which I searched for, and did not find anything useful)...

Does someone knows of such tool or a way of using DTS, or shall I write the all thing from scratch by myself?

p.s - I need the final result to be a standalone product

Thank you!

Answer 1

Hi Electric Light Orchestra,

An option for you could be to:
First, import the content of the Excel file (as it is) in a SQL table
Secondly, move rows with invalid value from that table to another one (log table).
All using T-SQL language - free to use with SQL Server Express Edition

Cheers,

Guillaume

Answer 2

Hi Electric Light Orchestra,

In addition to Guillaume.

SQL Server Integration Service(SSIS) is formerly known as Data Transformation Services(DTS). Yes, it can archive the goal you want.
However, it is not supported in SQL Server Express edition.

Thanks,
Jinchun Chen

---

Jinchun Chen(JC)
TechNet Community Support