-1

When I am trying to convert a flat-file to OLEDB in SSIS with visual studio 2019 my package is successfully deployed and executed in SSMS integration services catalogs, but when I do any transformations in between conversions those packages are not executing SSMS catalog even though the execution in visual studio is done without any errors and it was configured and validate successfully but not execute. I repaired, modify the Microsoft.Data.Tools.Integration services that one also not working. I am getting DataFlowTaskError:

To run SSIS package outside of SQL server data tools you must install standard edition(64-bit) of integration services or higher.

enter image description here

billinkc
  • 59,250
  • 9
  • 102
  • 159
Sravani
  • 13
  • 5
  • 1
    Run this query on your database and edit your answer to include the output `SELECT @@version;` – billinkc Feb 19 '21 at 05:18
  • 1
    https://stackoverflow.com/questions/35469249/to-run-a-ssis-package-outside-of-sql-server-data-tools-you-must-install-move-fil You can also check whether you have integrations services installed ? – Subbu Feb 19 '21 at 05:28
  • @billinkc I have run the query, it is showing me Microsoft SQL server 2019 – Sravani Feb 19 '21 at 09:52
  • @Subbu yes I have installed integration services – Sravani Feb 19 '21 at 09:52
  • @Sravani which sources are you using? Further it might be worth a try to change the property Run64BitRuntime of your SSIS project to false. – Tyron78 Feb 19 '21 at 10:02
  • @Tyron78 Sources in the sense I am using visual studio and SQL Server management studio. I tried it changing to false but I am getting the same error – Sravani Feb 19 '21 at 10:57
  • @Sravani no, I meant data sources in the dataflow. Several components feature a 32Bit and a 64Bit version, so this might lead to issues. e.g. if you have locally installed everything in the 64Bit version (VS, SSMS, etc.) but the server where you deploy the package to has only a 32Bit Version installed – Tyron78 Feb 19 '21 at 11:36
  • That's not what the query shows. Please post the full response from @@version e.g. `Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Nov 6 2020 18:19:52 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19041: ) (Hypervisor) ` – billinkc Feb 19 '21 at 15:13

1 Answers1

0

Based on your screen shot, you seems to have: 2 instances of SQL Server installed in your machine

  • first one with the instance name of "MSSQLSERVER01" and
  • the second one with the default name (so, the default name of "MSSQLSERVER")

It is likely you have installed the Integration Services component in the default instance alone, but trying to run it from the other instance.

You can cross check this by using the: SQL Server Configuration Manager application. It would show you all the different instances installed in your machine.

A sample screen shot from my machine showing multiple instances: enter image description here

So, a possible option is: Install Integration Services in your SQL Instance (MSSQLSERVER01) and try to run the package

OR

Use the other SQL Instance (the default one, MSSQLSERVER) for your SSIS packages.

Subbu
  • 2,130
  • 1
  • 19
  • 28
  • Integration services are installed for both instances, still didn't get any idea why it is not working!! – Sravani Mar 11 '21 at 08:03