by Jason Williscroft
Adam and I are working on hqTest, which is about to get installed at our first beta client. Woo hoo!
My job this weekend is to perfect the product’s initial test bed, which is a toy EDM pipeline realized in SSIS. (Why not MEDM? No dev licenses. Thanks, Markit!)
Anyway, it’s been a while, and the documentation is as confusing as ever, so I wound up getting the idea that I could install SQL Server Data Tools (SSDT) on top of Visual Studio 2015 and run SQL Server Integration Services (SSIS) packages against SQLEXPRESS.
And I could! It was sweet! Until I tried to run them from the command line, and got this error:
Turns out SSDT runs SSIS packages perfectly well from within Visual Studio because it invokes its own execution machinery. Run it from the command line and DTExec will be looking for the SSIS service, which is not part of SQLEXPRESS.
So dang. Looks like we need the big iron after all.
So I installed SQL Server Enterprise (Standard would do it, but what the hey) and tried again. Same result. Which is odd, since I could see the dang SSIS service running.
Uninstalled SQLEXPRESS and every related application I could find. Same result.
Repaired the SQL Enterprise installation. Same. At this point I’d spent way too many hours trying & failing to run something I knew worked, and I was getting steamed.
At last, on the DTExec MSDN page, I found this:
If you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility.
Of course DTExec uses PATH… I’ve been executing it without a path for hours! And it didn’t take long to jump to the idea that there might be some leftover PATH entry pointing to the SQLEXPRESS version of DTS.
And, of course, there was. The correct PATH entry for x64 is C:\Program Files\Microsoft SQL Server\120\DTS\Binn\, and a couple of entries above it was C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\, which had me trying to execute the x86 SQLEXPRESS version instead of the x64 big iron version.
This shouldn’t have been necessary. Just a sloppy SQLEXPRESS uninstall process by Microsoft, which ate up a good six hours of my weekend before Xmas. Thanks, guys!