Looking back at SQL Server ‘DTUtil.exe’ and PowerShell

Thanks to a SQL Server college that send me an email about a problem he was having understanding how I was using DTUtil in PowerShell and it was giving him the following error message:

The term ‘Create-My-New-Table’ is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:117
+ dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table
<<<<
    + CategoryInfo          : ObjectNotFound: (Create-My-New-Table:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

I was able to duplicate the error and after a couple of hours I realized that I failed to mention one critical piece of information.  In my previous blog series about running the DTutil.exe tool in PowerShell, I forgot to mention that you can’t run DTutil.exe in the PowerShell console.  This is one of a few *exe commands that was meant to run under DOS Shell.

That’s the reason why I came up with a PowerShell script that will call/execute a batch file with passing parameters in order to use the DTUtil.exe tool.

Now, to help clear some of the issues I found when trying to run this command with parameters:

1. As I verfiy, DTutil.exe will need to run in a batch file (*.bat) in order to be able to run in PowerShell.   Here’s the sample error message if you try to run DTUtil dos command under the PowerShell Console:

image

2. Don’t use IP address. In my test it didn’t work and in the Microsoft documentation it is asking for “servername”.  So, use the SQL “Servername” or “Servername\Instance”.   My test fail when using my local machine IP address:

image

3. Then, in the “.. /Copy SQL; ” . Don’t leave a space between the “ SQL; ” and the DTS Package name.

4. And, the DTS Package name doesn’t need the extension “*.dtsx”

Here’s example of a valid DTutil command I use to copy my sample DTS Package file to my local SQL Server:

dtutil /FILE “C:\temp\Store MySSIS Package\Create-My-New-Table.dtsx” /DestS MAX-PCWIN1 /copy SQL;Create-My-New-Table

I hope this will help understanding this issues of running this SQL DOS command in PowerShell and take a look at my older 3 part series about “Deploying SSIS Packages” in PowerShell:

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 1: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1075.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 2: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1102.entry

Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 3: http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!1133.entry

I greatly appreciate the emails and I’m glad to be able help in the IT community.

Keep PowerShelling!!