[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS 2008 and SSIS 2012

Posted on 2014-04-06
17
Medium Priority
?
602 Views
Last Modified: 2016-02-10
Dear all,

I install SQL server 2008 SSIS after I install SQL server 2012 on a test machine, but it seems that I can only connect to SQL server 2012 SSIS but not SQL server 2008 SSIS.

it seems that SQL 2008 SSIS don't have the instance name on the drop down name list of the SSMS.

anything wrong here?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39982529
Nothing wrong, unlike other SQL Server services SSIS is not instance-aware.  So you can only have one instance (the "default" instance) of each SQL Server version on the same machine.

In your case, to connect to SSIS 2012 you need to use SSMS 2012 and to connect to SSIS 2008 you'd use SSMS 2008.

More info: Interoperability and Coexistence (Integration Services)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39982643
"So you can only have one instance (the "default" instance) of each SQL Server version on the same machine.
"

I am already doing this kind of thing but it seems that it can find out the SSIS of one SQL edition.

I have one SSIS for SQL 2008 and one for SQL 2012.

"In your case, to connect to SSIS 2012 you need to use SSMS 2012 and to connect to SSIS 2008 you'd use SSMS 2008."

so use SSMS 2012 to connect to SIS2012 and SIS 2008 R2 using SSMS 2008 R2  ?

both edition install on my local machine.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39982647
but one thing is, SQL Server 2012 Integration Services (SSIS) can also connect to SSIS 2008 R2, how can we separate it?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39982829
so use SSMS 2012 to connect to SIS2012 and SIS 2008 R2 using SSMS 2008 R2  ?

Correct.

but one thing is, SQL Server 2012 Integration Services (SSIS) can also connect to SSIS 2008 R2, how can we separate it?

Normally not by default.  The following is an excerpt from the link I mentioned earlier:

You cannot connect to an instance of the SQL Server 2005 or the SQL Server 2008 version of the Integration Services service from the SQL Server 2012 version of Management Studio. You can use the SQL Server 2012 version of Management Studio to manage Integration Services packages that are stored in an instance of SQL Server 2008 or SQL Server 2012. You need to modify the service configuration file to add the instance of SQL Server 2008 to the list of locations managed by the service.

The choice of words is a bit confusing, but as far as I understand it unless you've modified the configuration file, SSMS 2012 will only connect to SSIS 2012.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39983389
"You need to modify the service configuration file to add the instance of SQL Server 2008 to the list of locations managed by the service."

here come a much interesting concept ! but how to modify the configuration files ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39983402
I just verify that I can connect to SSIS 2008 R2 using SSMS 2008 and SSIS 2012 using SMS 2012, the version number tell this!

but one thing, if we are architecting that from SSDT - BI tools for VS 2010 / SSDT - BT Tools for VS2012, can we connect or deploy package to both version of SSIS ?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39985214
but one thing, if we are architecting that from SSDT - BI tools for VS 2010 / SSDT - BT Tools for VS2012, can we connect or deploy package to both version of SSIS ?

No, packages developed with SSDT will not be compatible with 2008.  If your packages need to run on 2008 you'll need to use BIDS 2008 to develop them.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39985286
" If your packages need to run on 2008 you'll need to use BIDS 2008 to develop them. "

oh shit ! my BIDS 2008 can't start at all, it just keep saying SSDT is not installed and cna't start the workspace, which in fact the SSDT installed with all patches instlaled.

any idea on how to solve this?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39985370
here come a much interesting concept ! but how to modify the configuration files ?

Interesting question!

The SSIS 2012 configuration file is called MsDtsSrvr.ini.xml and by default it's located in %ProgramFiles%\Microsoft SQL Server\110\DTS\Binn.

To add your 2008 instance you'd need to include something like the following in the TopLevelFolders section:

    <Folder xsi:type="SqlServerFolder">
      <Name>The2008Instance</Name>
      <ServerName>The2008Server\TheInstanceName</ServerName>
    </Folder>

Open in new window

You'll need to modify the ServerName to your own specifics.

Please note that you will still not be able to actually "connect" to the 2008 SSIS instance.  But you will get an additional node in the Object Explorer when connecting to SSIS 2012.  This node will be called The2008Instance and will show the packages deployed to the 2008 MSDB.

Also note that you'll need to restart the 2012 SSIS service after changing the config file.

More info: Configuring the Integration Services Service (SSIS Service)

Ow, please make sure to create a backup copy of the original config file in case something goes wrong!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39985378
my BIDS 2008 can't start at all, it just keep saying SSDT is not installed and cna't start the workspace, which in fact the SSDT installed with all patches instlaled.

any idea on how to solve this?


No idea, I'd recommend to open a separate question for that issue...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39985963
"Please note that you will still not be able to actually "connect" to the 2008 SSIS instance.  But you will get an additional node in the Object Explorer when connecting to SSIS 2012.  This node will be called The2008Instance and will show the packages deployed to the 2008 MSDB.

Also note that you'll need to restart the 2012 SSIS service after changing the config file."

you mean I can't even modify it ?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39985974
Modify what exactly?  The name of the additional node?  That can be modified if you want, just change the Name property to what you prefer...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39987778
"Modify what exactly? "

config file, MsDtsSrvr.ini.xml

so just open it and edit it?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39987971
Yes, it's a text file (XML)...  But take a copy of the file first so you can always put that back to undo your changes if needed.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39988105
"But take a copy of the file first so you can always put that back to undo your changes if needed. "

yes, how to remark/comment the XML file? then I don't need to backup at all.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39988136
Why would you want to do that?  I'm not sure that it's supported but if you really insist, XML comments go like this:

<!-- comment -->

Open in new window

But I seriously recommend you to take a copy instead: CTRL+C, CTRL+V with the file selected and you've got a backup...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39988154
yeah ok !
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question