Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SSIS with SQL Server Express

Options
  • 09-04-2008 1:35pm
    #1
    cousin_borat
    Registered Users Posts: 995 ✭✭✭


    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?


Comments

  • Options
    #2
    kayos
    Registered Users Posts: 2,494 ✭✭✭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.


  • Options
    #3
    cousin_borat
    Registered Users Posts: 995 ✭✭✭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.


  • Options
    #4
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭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.


  • Options
    #5
    kayos
    Registered Users Posts: 2,494 ✭✭✭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.


  • Options
    #6
    Ginger
    Registered Users Posts: 2,931 ✭✭✭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


  • Advertisement
  • Options
    #7
    cousin_borat
    Registered Users Posts: 995 ✭✭✭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


  • Options
    #8
    Ginger
    Registered Users Posts: 2,931 ✭✭✭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


Advertisement