3

I'm trying to create an SSIS package to import xml data file into sql server table. I have sql server 2008 R2 and visual studio professional 2015 on Windows 10 machine. In VS Help About I can see SQL Server Data Tool 14.0.50730.0. I did an update via control panel just to be sure and the help list stayed the same. From loads of searching I'm guided to use New Project / Templates / Other Languages / Sql data tools - but this does not appear. Nearest I get is SQL Server/Sql Database project. SSDT does not appear in my start menu program list

How do I find and run sql server data tools so I can create a package?

WaterstarJohn
  • 31
  • 1
  • 3
  • To summarise the detailed answer by @bilinkc, you create SSIS packages for SQL Server 2008 R2 in "SQL Server Business Intelligence Development Studio". (which is a program called `devenv.exe`). This is on the install disk for SQL Server 2008 R2. When I do help/about I get "MS Visual Studio 2008 Version 9.0.3079". Yes - various versions SSIS development tools are a ridiculous mess. First thing to do is search for devenv.exe on your disk. – Nick.Mc Dec 07 '15 at 03:11
  • Hi Nick - Thank you, it surely is a mess. When I run devenv.exe I get VS2015. In control panel SQL Server 2008 / change it says I have a full install. In SQL Serverer MS help / about there is nothing about ssdt or ssis. I can't find anything remotely similar – WaterstarJohn Dec 07 '15 at 09:02
  • So what path is devenv in? I'll check mine when I'm back at work.You might find a few in different folders. Ddi you install any SQL 2008 client tools such as SQL Server Management Studio? – Nick.Mc Dec 07 '15 at 09:57

1 Answers1

3

You won't.

SSIS packages are bound to the edition (or upgraded to current) of SQL Server they are created against.

  • SQL Server 2005 uses VS 2005 to create SSIS packages.
  • 2008/2008R2 uses VS 2008. Both of those will be sourced from the SQL Server installation media.
  • SQL Server 2012 uses both VS 2010 and VS 2012 to author SSIS packages (same .dtsx file, just different color palates for designer). This can be installed from either the installation media or by downloading SSDT-BI
  • SQL Server 2012 uses VS 2013 to author SSIS packages and now the bits only come via the download
  • SQL Server 2016 will use VS 2015 to author SSIS packages and, breaking with the above pattern, will be able to create SSIS packages against earlier versions. (As of this answer, 2016 is not released but the speculation is that SQL Server 2012-2016 will all be able to targeted within VS 2015). These bits will also be available exclusively through download and there is a slight change in that process as you will be able to only download what you want (SSIS, SSAS, and/or SSRS)

If you Biml, then while you still need multiple installations of BIDS/SSDT/VS on your machine at least the problem just becomes a matter of "recompiling" to target the version instead of hand coding it per version of SQL Server you have to support.

Launching the correct visual studio

My start screen looks something like

enter image description here

Those are going to all point to the executable devenv.exe which, by default, will be in C:\Program Files (x86)\Microsoft Visual Studio * where you need to tie your year-version to internal number

enter image description here

You can find the installation locations for visual studio by issuing the following command dir /s /b devenv.exe from your C:\ location

C:\Program Files (x86)>dir /s /b devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\devenv.exe
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thanks for the detailed explanation of the versions which is a great start. You'll see in the comment above it looks like I have a full install of 2008R2 but I still can't find anything that looks like BIDS/SSIS/SSDT! – WaterstarJohn Dec 07 '15 at 09:10
  • @WaterstarJohn See edit - you need to launch the VS 2008 - default is `"C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"` – billinkc Dec 07 '15 at 15:31