Upgrading to SQL 2005 - Now using dts a lot

  • Hi

     

    We are now using dts a lot. From excel to SQL, from sql to sql. Having some dts packages.

     

    We were planning to upgrade to sql 2005 standard edition.

    But I've seen that microsoft calls dts now Integration services. And when i've taken  a look at the comparison table between the server versions, this module was only available in the enterprise edition. So is my understanding correct that you cannot upload an excel file into sql within the standard edition ?

     

    Thx in advance.

    El jefe


    JV

  • No, that's not correct. The Excel connection manager is available on all editions.

    K

  • Kirk is correct. Also, don't have to convert your DTS packages right away. There is an upgrade wizard, and dtsrun is still supported. 

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • JV,

    Just to clarify, SQL Server Integration Services (SSIS) is available in Standard edition right thru to Enterprise. The only place is you don't get it is on SQL Server Express.

    The Excel Connection Manager that Kirk spoke of is one of the "pieces" of SSIS that you will need to accomplish your requirement. And, as Kirk said, it is available in in all editions.

     

    -Jamie

     

  • Many thx guys.

    Will be cheaper 😉

    What's the big difference then ?

    Best regards

    J


    JV

  • JV,

    100 different people will most likely give you 100 different answers to that question. My answer would be "The biggest difference is the seperation of workflow from data-processing"

    You may find this article: http://www.sqlservercentral.com/columnists/jthomson/thenewetlparadigm.asp useful in giving an introduction to this fundamental change.

    -Jamie

  • On a related note - working with Excel into and out of SQL databases is a little harder in SSIS due to the Unicode/Non-Unicode translations. If you have a unicode database and are using nvarchar/nchar/etc types, you'll be fine. If you're using varchar/char/text/etc, you'll have to add in a Data Conversion step to convert between unicode and non-unicode. There's a wizard that will do this for you for one file, but I haven't really had a lot of luck working with a bunch of files this way.

    Really hoping that MS does something about this to allow implicit conversions in an upcoming SP - this doesn't classify as a step forward for the jobs I have to run.

    -Pete

  • I ran into this yesterday working on my first SSIS package. All I was trying to do is grab some records from a DB2 database and toss 'em in my SS05 table and it couldn't even handle that. Typical Microsoft style, make the tough stuff easy and the easy stuff a major PITA. Why are they so Unicode centric. Everyone is not international or set up for it. Not to mention space issues.

    Oh well. I was waiting for the first bomb to hit.

    Thanks for the tips and letting people know it isn't just them

    Gerald

  • Gerald,

    Some people like unicode, some people don't. Some people are not international - some people are. You can't please all of the people all of the time so they've gone for using whatever works for everyone (i.e. Unicode).

    If you want to change the default mappings then look at C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\IBMDB2ToSSIS.XML

    -Jamie

     

     

  • Thanks for that bit of information, Jamie. That's going to make a lot of my default mappings quite a bit less painful. I was always changing the nvarchar settings to varchar and it consumed quite a bit of time. Now if we just had a way to set some other defaults like the IMEX setting for Excel. 🙂 I really wish that was documented or hinted at in a little easier to discern manner. I never ran across those settings and just figured that these mappings were hard-coded in the program.

    -Pete

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply