0

I am attempting to run a simple SSIS package in Visual Studio (15.9.0). This is the first time I've used SSIS. My error message is at the bottom, because it's quite lengthy. Directly below is my attempt to fix the error.

I followed the directions of this link: https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

1.) I selected the checkboxes for "Data Storage and Processing" in Visual Studio Installer 2.) I uninstalled SSDT 3.) I downloaded SSDT for Visual Studio (15.8.2) 4.) I selected the "SSIS" checkbox on the installation of SSDT.

I am continuing to get the error message below. Thanks for any help in advance!

>

Failed to start project (Microsoft Visual Studio)

===================================

The directory name is invalid (Microsoft.DataTransformationServices.VsIntegration)

------------------------------ Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.LaunchVsDebugger(IVsDebugger iVsDebugger, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options) >

Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
Cooler
  • 11
  • 1
  • 4
  • 1
    Is this a new project? it seems that VS is complaining about a directory in the .sqlproj file. It's just text so it's easy to read and fix if need be – user1443098 Nov 26 '18 at 16:58
  • First make sure SQL Server is installed properly. Use SQL Server Management Studio (which comes with SQL Server) and make sure you can log into the Server. Did you download and install the full version of SQL Server? The SQL Service may not be set to start automatically so check Service and make sure the service is running. – jdweng Nov 26 '18 at 17:39
  • To user 1443098. Yes. This is the first time I've ever created an "Integration Services Project". I'm able to connect to the SQL Server database. Inside of my "Connection Manager", I can test the database connection and it succeeds. – Cooler Nov 26 '18 at 19:43
  • Yes, SQL Server is setup. I'm at work, so I don't have permissions to check if the SQL Service is running. I assume it is running, since I'm able to connect to the database. – Cooler Nov 26 '18 at 19:59

3 Answers3

2

Answer 2

See the response from Microsoft.

https://social.msdn.microsoft.com/Forums/Windowsserver/en-US/aa3a46fb-2f09-4808-b6db-baa1553f6401/failed-to-start-project-microsoft-visual-studio?forum=sqlintegrationservices

Answer 1 Based on the question and user comments, I think you may have installed SSDT incorrectly. When installing SSDT, you need to select Install SSDT for 2017 with a new instance of VS 2017. Then, Select Integration services (kind of mandatory here b/c thats what you are installing), Reporting Services and/or Analysis Services.

https://developercommunity.visualstudio.com/comments/345539/view.html

Update 1

SSIS has a long and tortuous history with Visual Studio integration - it used to just install as a new set of Business Intelligence projects (i.e., SSIS, SSRS, and SSAS) within Visual Studio itself, without requiring a new standalone installation (at least up through VS 2013, I never used VS 2015). Even the product's name has gone through a circus show: it went from DTS, to SSDT-BI, to SSDT - stay tuned for more changes from the marketing department folks!

As of VS 2017, SSDT has to be installed as a new instance of Visual Studio. This means that you will effectively have two instances of Visual Studio installed on your machine:

  1. For all SSDT related projects (i.e., Integration Services [IS], Reporting Services [RS], and Analysis Services [AS]).
  2. For all other non-SSDT related projects.

Note: I do not fully understand why Microsoft chose to split the development of SSDT into a standalone instance of VS 2017, but personally (and this is my own 2 cents) I hate it. The new Preview release cadence model has led to more problems than solutions and has made the relationship between VS2017 and VS2017 SSDT so unstable that I only install new preview releases on test boxes before installing them on my own. Microsoft has released some really bone-headed bugs (i.e., unable to deploy package from project, unable to open VSTA for script tasks/components due to regression issue with VS2017). It is enough to drive one bonkers. I currently use the following:

  1. Visual Studio 2017 (SSDT): 15.8.2
  2. Visual Studio 2017: 15.7.6
J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • I have Visual Studio Professional installed on my machine. Also, note that I'm not running SQL Server on my machine. I'm using SSMS on my machine to access SQL Server that's installed on another machine. I'm also running Windows 7 OS. – Cooler Nov 27 '18 at 14:48
  • I would like to compare your setup to mine. Are you running the full version of SQL Server on your machine? Or like my setup, is SQL Server installed on a separate machine and you're accessing it through SSMS? What version of Windows are you using? – Cooler Nov 27 '18 at 16:19
  • @user2372241 Wndows 7. SSMS is installed on my machine and I connect to SQL server instances on the network - no stand alone instances of SQL Server are installed on my machine. Where are you trying to run the SSIS package from: Visual Studio or SSMS? Note: in VS, you run the package in debug mode and you can watch it in real-time. In SSMS, you execute the package via a job in SQL Agent. – J Weezy Nov 27 '18 at 16:22
  • I'm running the package from VS. I've tried running the package by pressing the green play button and by right-clicking the SSIS package in Solution Explorer, but get the same result. I will install SSDT again and select "Install SSDT for 2017 with a new instance of VS 2017". – Cooler Nov 27 '18 at 17:03
  • I tried the installation of SSDT and selected "Install SSDT for 2017 with a new instance of VS 2017".and received "Setup Failed" error message. Does the installation of SSDT also install the slim down version of VS or do I need to download it? – Cooler Nov 27 '18 at 18:59
  • I have installed Vs2017 15.9.2 pro ; and just installed ssdt on top of that. No issues. Can create and deploy ssis packages to my desired server. – junketsu Nov 27 '18 at 19:18
  • @junketsu Can you create a script task/component and edit it? Whenever I click on the edit script button, it just hangs and then goes away - the VSTA editor does not launch. – J Weezy Nov 27 '18 at 19:53
  • @junketsu Ok, I can upgrade SSIS without problem and it works and even VSTA opens. But, now SSRS will not update because the log says certain folders are missing and will cause dependencies to break. Note: SSRS and SSAS have to be disabled/uninstalled before updating them. Just another nuance that makes updating this product suite tedious. I will wait for the next update to see if that resolves. Otherwise, users are left with having to uninstall and re-install, thus deprecating the idea of updating. – J Weezy Nov 27 '18 at 21:34
  • I updated my VS Pro from 15.9.0 to 15.9.2. I've started installing the SSDT tools. This time I chose "Visual Studio Professional" instead of "Install SSDT for 2017 with a new instance of VS 2017". – Cooler Nov 27 '18 at 21:59
  • After the VS update and SSDT installation, I'm still getting the same error. – Cooler Nov 28 '18 at 16:01
  • @cooler Questions. 1.) Can you create a new project with a end mail task that sends you a test e-mail? 2.) How long is the directory path? If it exceeds 256 characters then that might explain it. 3.) Did you create this package or did someone else create it? If received from someone else, can they run it on their machine? – J Weezy Nov 28 '18 at 16:11
  • 1.) I tried to run an end mail task and received the exact same error. 2.) When you say directory, do you mean the path where my project files reside? If so, the path is only 36 characters. 3.) I created the package 4.) The only person who can run an SSIS package is the DBA. My company is just starting to use SSDT. It seems like a permissions issue, but the error message doesn't say anything about permissions. – Cooler Nov 29 '18 at 15:12
  • @Cooler I have updated the answer with a second answer. – J Weezy Nov 29 '18 at 15:52
  • I added that assembly successfully to the cache. I restarted Visual Studio and I still get the same error. – Cooler Nov 29 '18 at 20:00
  • One final thing that is mentioned in that post is that, by default, the package's TargetServerVersion will be set for SQL Server 2017. If this is not your version of SQL Server, then you will need to change this. https://blogs.msdn.microsoft.com/sqlgardner/2016/08/17/ssdt-vs2015-gotcha-target-server-version-new-feature/ – J Weezy Nov 29 '18 at 20:57
  • My shop is running a SQL Server 2012 instance. Thus, I'm assuming I need to change the Target Server to "SQL Server 2012". The problem is that "SQL Server 2012" is not an option in the Target Server drop down list. Then, I've read that "VS Community 2015" has the option "SQL Server 2012" in the Target Server drop down list. Then, when I tried to install "VS Community 2015", I received an error saying that I can't download VS Community 2015, because I already have VS Professional. – Cooler Nov 30 '18 at 19:46
  • @Cooler SQL Server 2012 was removed from VS2017 SSDT 15.8.1 - you probably installed 15.8.2 on the recent round. You will need to uninstall 15.8.2 and go to 15.8.0 and stay there. Otherwise, goto Visual Studio 2012 or 2015 for continued SQL Server 2012 support. See 15.8.1 Changelog notes here: https://learn.microsoft.com/en-us/sql/ssdt/changelog-for-sql-server-data-tools-ssdt?view=sql-server-2017 – J Weezy Nov 30 '18 at 20:28
  • @Cooler See my expanded comment on SQL Server targeting through Visual Studio here: https://stackoverflow.com/a/53562698/4630376 – J Weezy Nov 30 '18 at 22:40
  • I'm getting an error uninstalling Visual Studio Professional from my machine. After it's uninstalled, I'll install 15.8.0. – Cooler Dec 04 '18 at 13:55
  • @Cooler Uninstalling and reinstalling the SSDT/VS stack can be challenging. See a posting here for some help/details (goto the solution, expand it, and see the comments at the end from Jeff.Harrington): https://developercommunity.visualstudio.com/comments/340582/view.html – J Weezy Dec 04 '18 at 16:13
  • I was able to uninstall VS. In case anyone else has this error...The error message said that I was missing a dependent package "Component.Microsoft.VisualStudio.Tools.Applications" version 15.0.27520. I found the program "Microsoft Visual Studio Tools for Applications 2017" with the same version number in Programs and Features. I right-clicked it and repaired it. After it repaired, I was able to uninstall VS. – Cooler Dec 04 '18 at 19:17
  • https://learn.microsoft.com/en-us/visualstudio/productinfo/installing-an-earlier-release-of-vs2017 I have only been able to find VS 15.7.6 at the link above. I installed it and it does not work with SSDT. Then, I tried to reinstall SSDT and I received an error saying I needed to have VS 15.8.0 or higher installed for SSDT 15.8.2. I have been searching for VS 15.8.0 and have not found a download link anywhere. Do you know where I can find it? By the way, thanks for all your help. I know this has been a very long thread. – Cooler Dec 05 '18 at 15:06
  • @Cooler it sounds like your package is still trying to run to the default target server of SQL Server 2017. Can you confirm that you have targeted your package to SQL Server 2012? – J Weezy Dec 05 '18 at 15:36
  • My goal is to target my package to SQL Server 2012. When I tried to open the SSIS Project, I received an error. It says "This version of Visual Studio is unable to open the following projects. The project types may not be installed or this version of Visual Studio may not support them. For more information on enabling these project types or otherwise migrating your assets, please see the details in the "Migration Report" displayed after clicking OK. I didn't see anything in the Migration Report that was helpful. – Cooler Dec 05 '18 at 16:46
  • @Cooler Close that project and create a new SSIS package. The migration feature is for packages that were created in previous versions of SSIS/SSDT-BI/SSDT and launches whenever you open a package that was created in a different version. The nuance here is that Visual Studio checks the version of SSDT within the package XML. Since you have installed the correct version of VS and SSDT for the correct version of SQL Server, this is a good thing to do. – J Weezy Dec 05 '18 at 19:05
  • I had VS 15.7.6 installed. When I tried to install SSDT, the message said I needed to have 15.8.0 or higher. So, are you sure VS 15.7.6 will work? I just want to make sure because installing/uninstalling VS takes forever on my machine. – Cooler Dec 06 '18 at 16:35
  • @Cooler You can run the Visual Studio 2017 update VS2017 to the most recent version (go through the VS2017 installer in the start menu) - I was able to do that on a fresh install lately (I just opted not to uninstall SSRS, which was my original problem). So, try the update to Visual Studio 2017 15.9.3. Then try to install SSDT 15.7.6. If that doesn't work. Get rid of Visual Studio 2017 and go to Visual Studio 2015 because it supports SQL Server 2012 and you are unlikely to go through the update hell that is VS2017. Alternatively, you can ask your firm to upgrade SQL Server to 2014 or higher. – J Weezy Dec 06 '18 at 16:50
  • Since, I can't target SQL Server 2012 with VS 15.9.3, I'll go ahead and install VS 2015 and look for a version of SSDT that is compatible. My firm is moving to a newer version of SQL Server in the near future. Then, I'll go back to VS 2017. – Cooler Dec 06 '18 at 18:29
  • @Cooler. Ok. Just to be clear, I meant VS 2017 15.9.3, while installing VS 2017 SSDT 15.7.6. Remember, two separate versions. – J Weezy Dec 06 '18 at 20:05
  • Oh. Oops, I definitely overlooked that. I don't see VS 2017 SSDT 15.7.6 anywhere to download. I went to the "Previous Versions" and can't find it on this link: https://learn.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-2017. Do you think one of the other versions on this link will work? – Cooler Dec 07 '18 at 14:51
  • @cooler 15.8.0 looks good. Note: the Changelog explicitly states under "Known Issues" that the installer for 15.7.1 fails for Windows 10. – J Weezy Dec 07 '18 at 16:00
  • I installed VS 15.9.3 and SSDT 15.8.0. My package failed with the same error. My firm will be updating to Windows 10 and to a new version of SQL Server in the near future. A coworker also suspects that permissions are being blocked somewhere as well. – Cooler Dec 11 '18 at 16:46
1

I finally got my SSIS package to run successfully! I was previously running Windows 7 64 bit OS and the version of SQL Server was 32 bit. I installed Windows 10 64 bit OS and now my SSIS package ran successfully. It seems that the OS and SQL Server must both be 64 bit.

Below are the different versions of technology I'm running

Windows 10 64-bit Operating System - SQL Server Standard 2012 (64-bit) - Visual Studio Professional 2017 (Version 15.9.4) - SQL Server Data Tools for VS 2017 (Version 15.8.2) - SQL Server Management Studio (Version 17.9.1) - The Project Template inside of VS was named "Integration Services Project" under the "Business Intelligence" tab.

Links

Cooler
  • 11
  • 1
  • 4
  • Glad to see you got it working. Clearly, Microsoft's error messages are erroneous themselves. A simple check for correct product versioning would suffice - I get that with other products. – J Weezy Dec 17 '18 at 15:57
  • 1
    Yes, I agree. Thanks again for all of your help! – Cooler Dec 17 '18 at 16:34
0

Script editor in VS 2017:::::::::::::::
enter image description here

junketsu
  • 533
  • 5
  • 17