Posts

Showing posts with the label agent

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr...
Post a Comment
Read more

Getting PowerShell to throw an exception in SQL Agent job

-
 By default, powershell scripts failing in a SQL agent job, won't stop the agent job. In otherwords, things will go wrong in your script, and SQL Agent will happily move on to the next step in the job. That is not so good. The options at your disposal would seem to be: Exit 1 Write-Error $ErrorActionPreference="Stop" throw Stuff that doesn't work The first two options might come to mind, but they don't work. Details below. Exit exit 1  (or any code != 0) would seem to give the calling application (Agent) the info that the task failed, by it's return code. However Agent doesn't respect this. So that's not an option. Write-Error Write-Error stops execution when you run a powershell script through a powershell window, but again, for SQL Agent, it doesn't pick up on that. Yes, it will stop the script, but Agent will happily continue to the next step Stuff that Works To get Agent to recognize a powershell script failure, and actually fail the agent job,...
Post a Comment
Read more

SSIS File Access Denied in C# Script Task

-
 I had an SSIS package which, as its first step, needed to pull a list of files to process. I did that with a C# Script Task that did that with Directory.EnumerateFiles on a specific fileshare directory. When I executed it locally (either through the Visual Studio project, or through DTEXEC on my local machine) it worked just fine. However when I deployed it to an SSIS Catalog, it would throw an error saying  <Script Task name>: Error: Access to path "..." is denied Same result if I executed it through an agent job on the server. Turns out, I was able to execute it just fine if I were connected to the actual server the SQL Instance the SSIS Catalog runs on. Why does this happen? Hell if I know. But that's the way it is.  For the agent job, it just need to be created as the SA (or an account with a lot of permissions), which my DBA could do. So, if you can get permissions to temporarily log into your SQL box, you can at least verify it will work. Then, if you have yo...
Post a Comment
Read more