locked
Migrating From SSIS 2005 to 2008 RRS feed

  • Question

  • The basics:

    Early days (2002) folks used Access and Excel to do their work.
    Got SQL2000, folks wrote DTS packages to do what they needed, with some of the source data still coming from Access and Excel.
    Migrated to SQL2005, folks keep running their DTS packages, although SSIS was installed. Several of us starting migrating some of the DTS packages to SSIS packages via BIDS.
     
    SQL2008 arrives, and is installed on our server, including Integration Server. I get tasked with checking everything out. On my machine I only have 2008 client tools, and BIDS. Everyone else is running 2005 tools and BIDS. I can remote into our server which has 2005 and 2008 tools, and BIDS. SQL2008 was installed as an instance, 2005 as the default.

    I've read that multiple instances of SSIS are not allowed, does this include 2005 and 2008 both running, or do they mean 2 instances of the the same version? My problem is trying to connect to the 2005 SSIS server via my box running 2208 client tools. I connect to the 2008 instance. If I try and connect using someone else's machine they get an invalid login id error (I'm the only login allowed on the 2008 server right now).

    I'm sorry if this is confusing, my background is VB6, and DB2 on the mainframe. Now I find myself swimming in the deep end.

    Bruce
    Friday, September 18, 2009 6:08 PM

Answers

  • SSIS doesn't have "instances", and they don't "run" on the server like SQL Server itself does.  (I'm confusing you more, I know.)  SSIS itself is almost entirely "just an app".  It's not a service that runs continuously, it's called on demand to run packages.  IS isn't really a "server".

    Since it's "just an app" and doesn't run all the time, the concept of "multiple instances" doesn't apply.  SQL Server supports multiple instances of the same or different versions on the same machine, because each instance is associated with a specific process running on the machine that "responds" to that instance "name".  No SSIS process is running until it's started with a specific command - "run this package" - and thereafter, you could consider it an "instance", but only in the same way as you would consider a currently executing VB6 app an instance of that app.

    Adding to the complexity is the incompatibility between 2005 and 2008.  You can't edit packages from one version in the other version's BIDS (Visual Studio).  So if you only have the 2008 client tools and BIDS on your machine, you can have issues connecting to 2005 and opening those packages.  Even "worse" is that you could automatically (and unknowingly) upgrade your 2005 packages to 2008, and then be unable to execute them on 2005, since it can't run 2008 packages.

    Clear as mud?  Fire away...
    Todd McDermid's Blog
    • Marked as answer by Bob Bojanic Monday, September 28, 2009 5:25 PM
    Friday, September 18, 2009 6:35 PM
  • If you want to work with 2005 packages, you must install the 2005 version of the client tools. The 2008 version only works with SSIS 2008 packages.

    You can have 2005 and 2008 installed side-by-side on the same machine.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by Bob Bojanic Monday, September 28, 2009 5:25 PM
    Friday, September 18, 2009 6:48 PM

All replies

  • SSIS doesn't have "instances", and they don't "run" on the server like SQL Server itself does.  (I'm confusing you more, I know.)  SSIS itself is almost entirely "just an app".  It's not a service that runs continuously, it's called on demand to run packages.  IS isn't really a "server".

    Since it's "just an app" and doesn't run all the time, the concept of "multiple instances" doesn't apply.  SQL Server supports multiple instances of the same or different versions on the same machine, because each instance is associated with a specific process running on the machine that "responds" to that instance "name".  No SSIS process is running until it's started with a specific command - "run this package" - and thereafter, you could consider it an "instance", but only in the same way as you would consider a currently executing VB6 app an instance of that app.

    Adding to the complexity is the incompatibility between 2005 and 2008.  You can't edit packages from one version in the other version's BIDS (Visual Studio).  So if you only have the 2008 client tools and BIDS on your machine, you can have issues connecting to 2005 and opening those packages.  Even "worse" is that you could automatically (and unknowingly) upgrade your 2005 packages to 2008, and then be unable to execute them on 2005, since it can't run 2008 packages.

    Clear as mud?  Fire away...
    Todd McDermid's Blog
    • Marked as answer by Bob Bojanic Monday, September 28, 2009 5:25 PM
    Friday, September 18, 2009 6:35 PM
  • If you want to work with 2005 packages, you must install the 2005 version of the client tools. The 2008 version only works with SSIS 2008 packages.

    You can have 2005 and 2008 installed side-by-side on the same machine.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by Bob Bojanic Monday, September 28, 2009 5:25 PM
    Friday, September 18, 2009 6:48 PM
  • SSIS doesn't have "instances", and they don't "run" on the server like SQL Server itself does.  (I'm confusing you more, I know.)  SSIS itself is almost entirely "just an app".  It's not a service that runs continuously, it's called on demand to run packages.  IS isn't really a "server".

    Since it's "just an app" and doesn't run all the time, the concept of "multiple instances" doesn't apply.  SQL Server supports multiple instances of the same or different versions on the same machine, because each instance is associated with a specific process running on the machine that "responds" to that instance "name".  No SSIS process is running until it's started with a specific command - "run this package" - and thereafter, you could consider it an "instance", but only in the same way as you would consider a currently executing VB6 app an instance of that app.

    Adding to the complexity is the incompatibility between 2005 and 2008.  You can't edit packages from one version in the other version's BIDS (Visual Studio).  So if you only have the 2008 client tools and BIDS on your machine, you can have issues connecting to 2005 and opening those packages.  Even "worse" is that you could automatically (and unknowingly) upgrade your 2005 packages to 2008, and then be unable to execute them on 2005, since it can't run 2008 packages.

    Clear as mud?  Fire away...
    Todd McDermid's Blog

    OK, I think things are becoming clearer. Setting aside the 2005/2008 combo for a minute. In BIDS2008 I import a DTS package. The transformation/importation works fine, I build the package and create a DTSX file. I can run this file inside of BIDS using the debug or not. I can also right click and say OPEN and it'll run in a cmd windows, or I can import it to SQL2008 and run it that way.

    The stuff we run is not high tech, we have 3 developers and 5 other folks who run these packages on occasion. We do run 7 days a week. I'm thinking of creating a central location on the server that everyone can get to, and having the packages reside there. Then whoever needs to run it will just navigate there and right click/open to execute the package.

    Is there any major stone wall I might run into trying to do it this way?

    Thanks,

    Bruce
    Monday, September 21, 2009 4:24 PM
  • First - to clarify - "building" the package doesn't create the DTSX file.  There is no "build" step in the SSIS world.  The DTSX file contains the complete instructions for executing your process, and gets consumed directly by the DTEXEC app.

    But to your main point - yes, you will likely have a problem.  SSIS is built (like most Microsoft products nowadays) with strong security built in.  You can save your DTSX files to a shared location just fine.  The problem comes in with "sensitive" information that's contained in the DTSX package files.  Stuff like connection string passwords.  If you're using Windows Integrated security to connect to everything, you'll be OK.  Odds are (with SSIS being an integration product) you're not...

    The default setting for packages in SSIS is to encrypt those sensitive settings with a hash key derived from the account that created the package.  This means that anyone else attempting to open the package will get a little error/warning message that they aren't that person, and therefore don't get to see/use the sensitive information that was contained in the package.  The net effect is that they won't be able to run the package without editing the connections and re-entering the passwords.  Every time.

    However, there are other options for that setting.  Here's the full documentation: Setting the Protection Level of Packages.  In your case, if these are not "really sensitive" and deserving of high security, it may be easiest (for convenience) to protect them with a package password (EncryptSensitiveWithPassword).  If you need higher security, you can start to use Package Configurations, and lock up the XML configuration files (containing the connection passwords) in a secured folder accessible only to those domain accounts you want.
    Todd McDermid's Blog
    Monday, September 21, 2009 5:03 PM