3

We have SQL Server 2012 on our test servers and I have developed a SSIS package in 2017 with Deployment Target Version set to 2012 on my local machine.

Within the package I have a mergeJoin object. The entire package runs nicely on my local machine, when I set it on test machine which via SQL server agent on 2012, it gives an error saying

To run a SSIS package outside of SQL Server Data Tools you must install Merge Join of Integration Services or higher

Integration services is installed on SQL Server 2012.

Is there a solution to this or if there is a workaround using merge join object, if I can replace with another object.. please suggest.

Sarah
  • 1,199
  • 2
  • 21
  • 42
  • Check the command line that's calling the package in your agent job and make sure it's looking for the correct version dtexec.exe. Similar issue here: https://stackoverflow.com/questions/35469249/to-run-a-ssis-package-outside-of-sql-server-data-tools-you-must-install-move-fil – Eric Brandt Sep 26 '18 at 20:14
  • I have limited access to the "test" server, but how can I check the command line? – Sarah Sep 26 '18 at 20:23
  • 1
    I see that your problem is solved, but in case you were still wondering, it requires access to the SQL Agent on the machine. Open the job. Open the step that executes the package. There are a number of tabs in the interface, and if memory serves the rightmost, or nearly so, tab shows the command line call that the job step is executing. But cheers to not having to bother. – Eric Brandt Sep 26 '18 at 21:52
  • Thank you so very much, Yes I see the command line tab under the job properties, so I just grab that and enter it in cmd? I actually dont see it having dtexec.exe at all. Its simply a /FILE "\"\\ path for the package /CONFIGFILE path for config file and then /CHECKPOINTING OFF /REPORTING E – Sarah Sep 26 '18 at 23:53

2 Answers2

2

Ok turned out that the test machine didn't have SSIS installed. Confusion is that there are other jobs using SSIS under SQL server agent that are running successfully on test, just this one with the merge Join is failing.

My conclusion is packages will run under SQL job agent that are simple without heavy work on machine that don't have SSIS installed, but in-order for package to run merge join or "not too straight forward" objects, SSIS has to be installed for a package to run.

Sarah
  • 1,199
  • 2
  • 21
  • 42
0

In regards to not using a Merge Join, if the data sources you are joining are from the same relational database, then you could just use a JOIN in your source query instead of using a merge join. If the sources you are joining are from flat files or different databases, then you need to load the data to a staging area first, and then create a new data flow task where you could use a JOIN in your source query to join the data sources.

Jeremy J.
  • 697
  • 4
  • 9
  • In my data flow, actually I have data coming in from two different source. One source is with db on Server 1 and other with db on Server 2. So I am trying to do a merge join of results from results from Server 1 and Server 2. – Sarah Sep 26 '18 at 19:26
  • 1
    I addressed this situation in my answer. If you can get past the merge join error, that would be ideal, but if you can't, then this is a simple workaround to avoid using a merge join. – Jeremy J. Sep 26 '18 at 20:07