July 23, 2012 at 10:59 pm
Has anyone upgraded from SQL 2008 R2 to SQL 2012, including SSIS? I've run the upgrade with no errors, but now have 2 SSIS services - versions 10.0 and 11.0.
What I was expecting, based on this (http://technet.microsoft.com/en-us/library/cc879336(SQL.110).aspx), was a single upgraded service.
Has anyone else seen this?
Thanks
July 25, 2012 at 10:00 pm
I think it's very unlikely that anyone else will have this problem but just in case! What seems to have happened is that SSIS was installed as 'eval', rather than Enterprise, like the rest of the server. You can't upgrade from eval, consequently - 2 instances of SSIS.
July 25, 2012 at 10:42 pm
I was following this thread wondering what was the outcome. Thanks for posting back. Happy you got it sorted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 1, 2012 at 7:37 am
I did an in place upgrade from 2008 R2 to 2012 and got the same thing, two instances of SSIS running side-by-side. This appears to be by-design and actually turned out to be a blessing in disguise.
We have over 1,000 SSIS packages and 2012 will run most 2008 R2 packages with no problem; however, I didn't have the time to test the upgrade of all those packages. Have the two instances on the SSIS server enabled us to leave all the 2008 R2 packages in place and upgrade them on a project-by-project basis. Additionally, we can convert the SSDT projects to "Project Deployment Mode" as we move them to 2012 rather than maintaining all the clutter of the legacy "Package Deployment Mode".
Let me know if you need any more details. I will be glad to answer any further questions you might have around this topic.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
August 1, 2012 at 9:53 pm
That's helpful to know, thank you. In my case, I also found that I was getting errors around VSTA, and couldn't upgrade or run any packages on from within SQL 2012 if they contained script tasks. I tried quite a few things to fix this, but in the end decided that a clean(ish) reinstall was the safest option. It now works fine for both SQL 2008 R2 and SQL 2012 packages, with only a single v11.0 IS service.
All the documentation that I've read so far suggests that all services are upgraded during an in-place upgrade. I'd be interested to know if you've found anything from Microsoft that suggests otherwise, especially if they have a best practice for what to do with the IS 10.0 service once all the packages have been migrated/upgraded.
August 1, 2012 at 10:30 pm
Tatsu (8/1/2012)
I did an in place upgrade from 2008 R2 to 2012 and got the same thing, two instances of SSIS running side-by-side. This appears to be by-design and actually turned out to be a blessing in disguise.We have over 1,000 SSIS packages and 2012 will run most 2008 R2 packages with no problem; however, I didn't have the time to test the upgrade of all those packages. Have the two instances on the SSIS server enabled us to leave all the 2008 R2 packages in place and upgrade them on a project-by-project basis. Additionally, we can convert the SSDT projects to "Project Deployment Mode" as we move them to 2012 rather than maintaining all the clutter of the legacy "Package Deployment Mode".
Let me know if you need any more details. I will be glad to answer any further questions you might have around this topic.
Were you continuing to run your 2008 packages using dtexec 10.0? From the file system or were they carried forward in the upgrade and allowed to stay in msdb? If msdb, can you deploy new versions of 2008 packages into msdb on the 2012 instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 5:44 am
opc.three (8/1/2012)
Were you continuing to run your 2008 packages using dtexec 10.0? From the file system or were they carried forward in the upgrade and allowed to stay in msdb? If msdb, can you deploy new versions of 2008 packages into msdb on the 2012 instance?
We continue to run our 2008 R2 packages that are stored in MSDB via the 10.0 (or 10.5?) DTExec utility. We also have a few 2012 packages in MSDB that we run from the 11.0 DTExec. We'll probably start converting SSIS projects to Project Deployment Mode in the next couple weeks so we won't have a lot of overlap in MSDB. You can continue to deploy 2008 R2 packages to the package store.
The thing you have to remember is that the 2008 R2 tools (BIDS, SSMS, DTExec, and so on) connect to the 2008 R2 SSIS instance and the 2012 tools connect to the 2012 instance. They both share the MSDB so you can have packages of different version in the same package store.
Another thing to consider, if you have SQL Agent jobs running on the same server they're probably going to run the packages through the 2012 instance. I hadn't thought about that before because we use a centralized, cross-platform scheduling package rather than SQL Agent. The developers are responsible for making sure that the scheduled jobs are calling the right DTExec for each package.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
August 2, 2012 at 2:52 pm
Tatsu (8/2/2012)
opc.three (8/1/2012)
Were you continuing to run your 2008 packages using dtexec 10.0? From the file system or were they carried forward in the upgrade and allowed to stay in msdb? If msdb, can you deploy new versions of 2008 packages into msdb on the 2012 instance?We continue to run our 2008 R2 packages that are stored in MSDB via the 10.0 (or 10.5?) DTExec utility.
dtexec for 2008 will be v10.0. For 2008 R2 it will be v10.5.
You can continue to deploy 2008 R2 packages to the package store.
Good to know. That is what I was hoping to get confirmation on. Thanks.
The thing you have to remember is that the 2008 R2 tools (BIDS, SSMS, DTExec, and so on) connect to the 2008 R2 SSIS instance and the 2012 tools connect to the 2012 instance. They both share the MSDB so you can have packages of different version in the same package store.
Another thing to consider, if you have SQL Agent jobs running on the same server they're probably going to run the packages through the 2012 instance. I hadn't thought about that before because we use a centralized, cross-platform scheduling package rather than SQL Agent. The developers are responsible for making sure that the scheduled jobs are calling the right DTExec for each package.
I tested it from the command line and dtutil.exe v10.5 can deploy packages to msdb while the IS 10.0 service is off. Similarly dtexec.exe v10.5 can execute packages while the service is off so I think you can disable the IS 10.0 service unless it is required for BIDS' benefit.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 7:11 pm
Just to say - I finally got the chance to do a completely vanilla upgrade from SQL 2008 R2 and again ended up with 2 IS services. 🙂
August 3, 2012 at 6:10 am
JKG (8/1/2012)
All the documentation that I've read so far suggests that all services are upgraded during an in-place upgrade. I'd be interested to know if you've found anything from Microsoft that suggests otherwise, especially if they have a best practice for what to do with the IS 10.0 service once all the packages have been migrated/upgraded.
You're right about the documentation, it's pretty terrible. The closest thing I can find is a hint at best:
The upgrade process does the following tasks:
- Upgrades the Integration Services files, service, and tools (Management Studio and SQL Server Data Tools). When there are multiple instances of SQL Server 2005 or SQL Server 2008 on the same computer, this upgrade occurs when the first instance of the Database Engine is upgraded.
- Removes the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) files, service, and tools after the upgrade process finishes.
Notice that the second bullet doesn't mention 2008 R2. I don't know if that is intentional or an error of omission but I am posting some information in the community content of the above linked article to help clarify.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
August 6, 2012 at 8:09 pm
I've run into this issue as well. I have 2008 R2 (Developer Edition), and I installed the developer of 2012 a few days ago.
I'm still able to run the packages I developed in R2 which are deployed in the file system, but I can't connect to the SSIS instance in SSMS or in BIDS. I get the 'Access is Denied' error even though I'm an admin on my local machine. I've checked all the permissions for the COM stuff that Microsoft recommends, and I should be good.
I've stopped the R2 instance of SSIS because I thought the connection might be colliding somehow, and it didn't help (even after restarting the 2012 SSIS instance). I also tried stopping the 2012 SSIS and re-starting the R2 one, and it didn't help either. I can't even connect to the R2 package store now.
Not sure what to do at this point. I've yet to see any documentation for this error. I was thinking of uninstalling the R2 instance of SSIS, but from seeing this thread, it appears that it's not necessary?
At this point, I've played all my cards, and I have no clue as to where to go. It's especially frustrating since I just got through fighting all the SSRS UAC restrictions garbage. Feels like Microsoft hasn't thought permissions through very well.
August 10, 2012 at 7:53 pm
orpheussail (8/6/2012)
I've run into this issue as well. I have 2008 R2 (Developer Edition), and I installed the developer of 2012 a few days ago.I'm still able to run the packages I developed in R2 which are deployed in the file system, but I can't connect to the SSIS instance in SSMS or in BIDS. I get the 'Access is Denied' error even though I'm an admin on my local machine. I've checked all the permissions for the COM stuff that Microsoft recommends, and I should be good.
I've stopped the R2 instance of SSIS because I thought the connection might be colliding somehow, and it didn't help (even after restarting the 2012 SSIS instance). I also tried stopping the 2012 SSIS and re-starting the R2 one, and it didn't help either. I can't even connect to the R2 package store now.
Not sure what to do at this point. I've yet to see any documentation for this error. I was thinking of uninstalling the R2 instance of SSIS, but from seeing this thread, it appears that it's not necessary?
At this point, I've played all my cards, and I have no clue as to where to go. It's especially frustrating since I just got through fighting all the SSRS UAC restrictions garbage. Feels like Microsoft hasn't thought permissions through very well.
The permissions are fine, where Microsoft dropped the ball was with the documentation.
If you want to connect or deploy to the 2008 R2 SSIS instance you need to use 2008 R2 tools (SSMS and BIDS 2008 R2). The 2012 tools (SSMS and SSDT 2012) will only connect to the 2012 instance.
It's not complicated but it's certainly not obvious!
If you want to remove the 2008 R2 instance it shouldn't be a problem. You'll want to make sure you upgrade all your packages. When SSIS 2012 executes a package it upgrades it in memory before it runs it but does not save the upgraded version. I don't know just how much of a performance hit you take for it but it's never a good idea to waste resources if you can prevent it.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
August 12, 2012 at 4:24 am
This seems to work for managing 'legacy' SSIS packages through 2012 SSMS:
http://www.ssistalk.com/2012/03/21/connecting-to-a-pre-sql-2012-ssis-instance-with-sql-2012-ssms/[/url]
In case this is use to anyone else: I've had some problems with running legacy packages after upgrades. According to MS documentation, a SQL 2008 or later package can be run on a SQL 2012 server; IS 2012 will temporarily upgrade the package, run it, and then undo or discard the changes. I was finding that this didn't work for the instances of SQL that I've upgraded in-place, at least for packages containing script tasks. Script tasks will fail validation; if I tried to upgrade the package on the server, using SSDT, I'd get this error: 'Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications.Core, Version=10.0.0.0'.
After doing a few test installs and upgrades, I've come to the conclusion that you can only run a legacy package that contains a script task if you install Data Tools and Integration Services at the same time. The x86 version of VSTA 3.0 doesn't seem to get installed if you install SSDT after IS, which might have something to do with it (the servers I've been working on had IS but not BIDS installed by the way). I've tried reinstalling IS and SSDT, and that doesn't work either. My workaround for this has been to uninstall IS before upgrading, and then install IS 2012 and SSDT after the upgrade. I can then run legacy packages with no issues.
If anyone's had the same issue and/or found a better workaround/fix, I'd be interested to know.
April 14, 2016 at 5:16 pm
While this is an old thread, I am hoping you will reply. I currently have an application server running SQL Server Integration Services service (SQL Server 2005 (90)). For testing and other purposes, I will also need to concurrently run SQL Server Integration Services service (SQL Server 2012(110)). The later will be installed on top of the 2005 install. The goal is to be able to run both 2005 and 2015 SSIS packages against their respective servers, with each package launched with the appropriate version of dtexec and utilizing the appropriate SSIS service version.
April 14, 2016 at 8:58 pm
Your configuration is going to be uncommon to say the least (SQL 2005 is beyond end of life). Any response on a Forum is going to be heresay from the perspective of your servers. Do you have a server where you can test? You need to test, test, test...and then post your findings back here for the next person that gets stuck in your situation.
I would also recommend you start a new thread on this Forum and optionally add a link to this thread for reference. I think you'll get more page views that way and potentially more assistance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply