Solved

SSIS development

Posted on 2011-09-30
33
1,187 Views
Last Modified: 2016-02-15
in http://msdn.microsoft.com/en-us/library/ms173767%28v=SQL.105%29.aspx
it says "Business Intelligence Development Studio is Microsoft Visual Studio 2008"

does that mean there is any practical inter-dependence between SSIS and VS?

2005 SSIS<->2005 VS
2008 SSIS<->2008 VS
2008 R2 SSIS<->2008 VS

for the next release will it be
2011 SSIS<->2011 VS - does this matter to know? or practically, is SSIS development totally independent if VS is installed or not?
0
Comment
Question by:25112
  • 16
  • 6
  • 5
  • +3
33 Comments
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 75 total points
Comment Utility
For the next version of SQL it will be used VS 2010.
Regards,
Pedro
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>For the next version of SQL it will be used VS 2010.
have they mentioned it already? how does it practically affect/impact SSIS development?

will developing 2011 SSIS [package require any VS 2010 install on the machine, also?
0
 
LVL 21

Assisted Solution

by:huslayer
huslayer earned 75 total points
Comment Utility
it'll install it's own 2010 BIDS, which is smaller version of VS 2010 to develop SSIS< SSRS and SSAS for SQL 2011 (Denali)
0
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
The future SQL version (DENALI) is available for download. For now is just the Comunity Preciew (CTP)
The final version (RTM) will be announced january 2012

The new SSIS 2012 required VS 2010.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
so BIDS is always part of Visual Studio? But it is given in the SQL CD, always to make it convenient?

that means you can instsall BIDS without having to install Visual studio. example:
Can you install 2005 BIDS and not install VS 2005
Can you install 2008 or R2 BIDS and not install VS 2008
Can you install Denali BIDS and not install VS 2010
0
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
YES!
:-)
BIDS Its a module of Visual Studio and is installed with SQL!

:-)
0
 
LVL 5

Author Comment

by:25112
Comment Utility
OK.. does BIDS through SQL influence which VS version can be installed or can exist in the machine?

for example, if I have 2008 R2 BIDS (from SQL install, not visual studio install), and also 2005 BIDS (from SQL install, not visual studio install).
then if I want to put VS 2003/5/8/10 on the same machine, will there be any conflicts? (because BIDS is already there)
0
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
It's optional the instalation of BIDS through SQL Install.
Why you want multiple versions of VS?!
Regards
Pedro
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I (and others I know) have run into issues when BIDS 2008 was installed on the same box that I had BIDS 2005 on.  BIDS 2008 modifies some components that are held in common with BIDS 2005 and then they don't work with BUIDS 2005.

It should also be noted that you cannot develop a package in BIDS 200Y and then deploy it to a SQL Server 200Y instance.  In other words, if the SSIS package is developed in BIDS 2005, it can only be deployed to SQL Server 2005 instances.  If you then open it and buildit in BIDS 2008, a) it can only be deployed to SQL Server 2008 instances and b) it can no longer be built using BIDS 2005 nor can it be deployed to a SQL Server 2005 instance.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>It's optional the instalation of BIDS through SQL Install.
but I just want to confirm that installing BIDS from SQL 2005 first and then the whole of VS2005 won't give any troubles? (or vice versa, if installed VS 2005 first, and then BIDS is installed as part of SQL 2005 complete installation).

>>Why you want multiple versions of VS?!
may not. just trying to determin how it affects all, and which ones are absolutely needed for development
0
 
LVL 5

Author Comment

by:25112
Comment Utility

>>I (and others I know) have run into issues when BIDS 2008 was installed on the same box that I had BIDS 2005 on.  BIDS 2008 modifies some components that are held in common with BIDS 2005 and then they don't work with BUIDS 2005.

have you got a resolution for that?

if you install 2008 BIDS first and then 2005 BIDS, is it safer and better?
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>> you cannot develop a package in BIDS 200Y and then deploy it to a SQL Server 200Y instance.

this does not apply to next release, right?BIDS 2010 and a SQL Server 2012(denali)
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 175 total points
Comment Utility
have you got a resolution for that?
Yeah . . . I have a virtual desktop for the 2008 development and my physical desktop for the 2005. ;-)


if you install 2008 BIDS first and then 2005 BIDS, is it safer and better?
Nope, that doesn't work either.

this does not apply to next release, right?

I don't know how you made that leap.  As far as I jknow, this is a fairly permanent rule.  you need to match the BIDS version with the SQL Server version . . . period.

And, yes, I know, that is a bit of a pain but, apparently, MS figures that you need to have one version in use in your world and then, when you upgrade to another version you will cease developing for the earlier one and convert your world to the newer one.  (MS Tends to live in a fantasy world sometimes. ;-)
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>Yeah . . . I have a virtual desktop for the 2008 development and my physical desktop for the 2005. ;-)
is there any solution if VM is not possible/allowed in this environment?

>>I don't know how you made that leap.
Isn't BIDS 2010 that is going go with SQL 2012 (denali)?
http://social.technet.microsoft.com/wiki/contents/articles/1740.aspx
0
 
LVL 5

Author Comment

by:25112
Comment Utility
@8080_Diver,

>> BIDS 2008 modifies some components that are held in common with BIDS 2005 and then they don't work with BUIDS 2005.

Has MS admitted this issue- that BIDS 2005 and 2008 cant be happy together on the same machine;if so, i could use to give it to management to re-think the whole scenario.. ? thanks..
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
From the link you posted:
2.2 BIDS Is Not Supported in Side-By-Side and Upgrade Scenarios
Business Intelligence Development Studio (BIDS) is not supported in the following situations:

•Microsoft SQL Server Code-Named “Denali” CTP1 BIDS is installed on the same server as an earlier release of MS SQL Server BIDS.

•An earlier release of Microsoft SQL Server with BIDS is upgraded to Microsoft SQL Server Code-Named “Denali” CTP1.

Make sure you create and maintain BIDS projects on computers that do not fall into the categories above.


In other words, no, Denali BIDS does not play well with any other versions of BIDS.

As for a non-vm solution to the incompatabilities, the only other sure solution I can think of is to have a second box to work on.  You might be able to use a dual boot boot to handle it but I doubt it.

Has MS admitted this issue . . .
Well, it would seem so . . . after all, there is that post about Denali BIDS. ;-)

I can see some issues for those who are doing Road Warrior work or supporting multiple small business.  One has to deal with the SQL server versions that one's clients have and one cannot necessarily dictate which version that will be.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Author Comment

by:25112
Comment Utility
thanks.

do you see such documentation for R2 because that is the version I really need to make my case for:
http://download.microsoft.com/download/E/7/D/E7DED7E4-5059-4E3E-82C5-B28BF3FD0AAC/SQLServer2008R2ReleaseNotes.htm
http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2008-r2-sp1-release-notes.aspx

Also, could you summarize what were the laymen terms symptoms you saw when you had BIDS 2005 and also BIDS 2008 in the same machine.
you create in 2005 and then it won't work? (and vice versa?)
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Re: The Case For 2008R2
- For openers, once 2008 was released, SQL Server 2005 has at most 5 years from that date before support is suspended.  Since 2008R2 is now out, 2008 has 5 years before support is removed.  As a general rule, if you are going to set up a database, you need to set it up on the most current version currently available.  If you are going to upgrade, then upgrade to the most current available.

- If your organization is on SQL Server 2000, then it should be noted that SQL Server 2008R2 is the last version that will have a ready upgrade path . . . Denali doesn't even know SQL Server 2000 ever existed. ;-)

- If y'all are looking at SQL Server 2008, y'all might as well go to 2008 R2. ;-)

- The new Date, Time, and new DateTime datatypes will make many aspects of life much easier to deal with and queries more efficient because you won't have to constantly be trimming the time portion off of a datetime datatype that has been filled with GETDATE().

- The new goegraphic datatypes and the functions associated with them open up a whole new aspect of using the data in the database.  For instance, if you hhave a web site that is receiving orders or inquiries, you can determine the gegraphic locations of the users and do things like target certain regions/locations/cities/whatever for offers or other special purposes.

- The BI tools available with 2008 R2 are quite impressive and much better than those available with 2005.

Re; Laymen's terms
What I ran into was that I had developed some packages in BIDS 2005.  I then needed to develop a couple for a 2008 database, so I installed BIDS 2008.  That worked out okay for the 2008 SSIS development; however, when I tried to open up my 2005 packages, I got error fatal messages and couldn't get them to open properly.  In fact, at one point, I corrupted one of the packages while trying to get it to open up in BIDS 2005.  In order to revert to BIDS 2005, I had to uninstall BIDS 2008 and then uninstall what I could of BIDS 2005.  Then I had to search out the various bits that didn't uninstall correctly for both of those.  I was finally able to re-install BIDS 2005.  (Being a persistent and stubborn cuss, I went through this cycle 3 times trying to work out a way to get both on on box. ;-)

0
 
LVL 5

Author Comment

by:25112
Comment Utility
Thanks for your explanation.. i agree R2 is the best and way to go, if at all possible with the management policies...

*could you provide any msdn link that 2008 R2 components share the same with 2005 (and hence the problems)

the Denali link is good, knowing some here, i know that some may say that is only for future versions..
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I don't have a link to the shared component list . . . it is kind of one of those "oops, that borke! :-/ " things.

0
 
LVL 5

Author Comment

by:25112
Comment Utility
OK.. no problem.. thanks for trying..

could you help with the below:
1)
is BIDS the only point of conflict in the Visual Studio family? Do any other tools clash like this? If VS 2005 and 2008 are on the same machine, are there are any issues like this (other than BIDS)?

2)
If SQL 2005 complete installation (including BIDS) and SQL Server 2008 complete Install minus BIDS OK?
(and vice versa) - If SQL 2008 complete installation (including BIDS) and SQL Server 2005 complete Install minus BIDS OK?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
One thing that should be pointed out is that you can install both SQL Server 2005 / 2008 R2 client components and continue to develop SSIS packages using VS 2005 to ultimately run using a 2005 or 2008 R2 database.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
Hi,

When you install SQL server whatever version 2005 or 2008, BIDS (VS Shell 2005 or 2008 ) comes with it. No need to install it separate. If you have installed server on different machine and need to develop on different machine then BIDS shell needs to be installed.

0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>One thing that should be pointed out is that you can install both SQL Server 2005 / 2008 R2 client components and continue to develop SSIS packages using VS 2005 to ultimately run using a 2005 or 2008 R2 database.

are you suggesting to develop only in one version (2005)?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>are you suggesting to develop only in one version (2005)? <<
No, I did not say that.  I said you can continue to develop SSIS packages using Visual Studio 2005 using either SQL Server 2005 or 2008 or 2008 R2 (or Denali for that matter)
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>you can continue to develop SSIS packages using Visual Studio 2005 using either SQL Server 2005 or 2008 or 2008 R2 (or Denali for that matter)

that makes sense.. because 2005 or 2008 or R2 database is 'only' a database for a SSIS package, regardless of the version of the connection string servers, right?

but the question is about the concerns when there is also vs2008 bids on that same machine where vs 2005 is used to make that package..
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>but the question is about the concerns when there is also vs2008 bids on that same machine where vs 2005 is used to make that package.. <<
Correct, if you need to maintain SSIS packages written in Visual Studio 2005 you will need to keep that development tool available.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
>>Correct, if you need to maintain SSIS packages written in Visual Studio 2005 you will need to keep that development tool available.

OK, I think you mean that if it is developed in 2005 BIDS, then always keep that tool. But it may break when 2008 is also introduced in that machine?

With R2 SSIS, can you make connection strings to all the prior version databases (upto SQL 2000).
Can you do the same connection strings from SSIS 2005. (connect to 2000, 2008, R2, 2005 databases all from one SSIS 2005 package)?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 175 total points
Comment Utility
>>But it may break when 2008 is also introduced in that machine?<<
It will "break" if you open with Visual Studio 2008 any package written by Visual Studio 2005.

>>With R2 SSIS, can you make connection strings to all the prior version databases (upto SQL 2000).<<
Let me restate that:  When using SSIS, you can open any database for which you have the appropriate driver.

>>Can you do the same connection strings from SSIS 2005. (connect to 2000, 2008, R2, 2005 databases all from one SSIS 2005 package)? <<
I have no idea.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
thanks acperkins.

8080_Diver, can you please confirm the questions on post # 3689363. then this post can be closed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I suspect you mean #36893639 and those questions have been answered more than once, but if you want to hear it from 8080_Diver that is fine with me.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
1)
is BIDS the only point of conflict in the Visual Studio family? Do any other tools clash like this? If VS 2005 and 2008 are on the same machine, are there are any issues like this (other than BIDS)?

Since I am a Development DBA annd primarily work with BIDS, no, I do not know of any other conflicts.  However, that being said, I rather suspect that there may be . . . MS has that kind of habit. ;-)

2)
If SQL 2005 complete installation (including BIDS) and SQL Server 2008 complete Install minus BIDS OK?
(and vice versa) - If SQL 2008 complete installation (including BIDS) and SQL Server 2005 complete Install minus BIDS OK?


As far as I know, the answer to both is, "No/".  Again, though, I am a Development DBA and not an application or web developer.  Therefore, I am providing the information that I know.

Given the above questions, in general, it does not pay to try to install multiple versions of a given Microsoft development tool on a single system unless you really know what you are doing . . . and from the questions (no offense intended), you don't seem to be in that category.  

I had a really horrible experience trying to use BIDS 2005 and 2008 on the same machine and the installs were done by our support team who are really very sharp.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
thanks all!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now