SSIS with SQL Server Express

cousin_borat

Planning how to migrate data and also set up a recurring ETL process to move data from a flat file dump into a database schema in SQL Express.

I know that SSIS not included with SQL Server expreess. However, I'm wondering whether it's possible to get it to work with SQL Server express? At the moment we're using a SQL express database which will be migrated to Server 2005 later.

If SSIS does not work are there any other alternative for migrating data?

kayos

Dont see why SSIS would not be able to connect to a express DB. Considering all the providers available to you. Heck if it will connect to access I doubt MS would not allow sql express to be used.

cousin_borat

kayos wrote: »

Dont see why SSIS would not be able to connect to a express DB. Considering all the providers available to you. Heck if it will connect to access I doubt MS would not allow sql express to be used.

I'm sure it can connect to SE2005 as a data source. However in this instance an Excel flat file would be the source and SE2005 would be the target.

SSIS ships with the standard and enterprise editions.

stevenmu

Does the layout of your excel file match your DB schema? If so open your table(s) in SQL Server Management Studio express and you can just copy and paste from excel.

Otherwise .Net code can access an Excel sheet as a datasource, write a quick utility to load the excel data up to a dataset, make any changes you need to and fire it back into SQL Server.

kayos

cousin_borat wrote: »

I'm sure it can connect to SE2005 as a data source. However in this instance an Excel flat file would be the source and SE2005 would be the target.

SSIS ships with the standard and enterprise editions.

Well just I popped open SSIS and connected to a SE2005 DB using the Native OLE DB\SQL Native Client provider. you should have no problems.

While SSIS only ships with certain versions it does not restrict its use to those versions for either source or destination. Hell if you write a provider yourself you could connect to anything you want.

Ginger

Have a look at BCP. You can use this to import your data from your flat file as long as you know the layout and it wont change. Its an oldie but a goodie!

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

cousin_borat

Thank's for the replies. Going to take a look at the suggestions.

The scenario is as follows:
The source is a Single Excel files
The target is SQL Express 2005

The migration will split the large excel table into three target tables in SE2005
There will be transformation of column values in the base excel table to alternative values

At the moment its done manually

Ginger

You can save as CSV

BCP to a large table, run the stored procs to bring your data into relevant tables that will allow conversions as required..

Also can use a format file