SSIS handling errors in control flow

To ensure that errors don't cause the package to stop, but all errors are reported back to parent containers, set the following:

FailParentOnFailure = True
MaximumErrorCount = 99999 (an "infinite" value)

SQL Server Agent Jobs and GUI Automation

Scenario

An application vendor has provided an application database maintenance utility that only has a GUI interface. You want to automate this maintenance activity as a SQL Server Agent job.

The best solution would probably be to tell the vendor to add a command line interface to the utility to facilitate unattended use. Unfortunately the vendor may be unable or unwilling to achieve this.

A Solution

1. Use AutoIt to script and compile an executable that will perform unattended execution of the ute
2. Call the AutoIt-generated exe from a SQL job using CmdExec

Step 1 is challenging, particularly if the vendor's utility is complex or poorly documented. Step 2 can be difficult to implement due to environment constraints.

With AutoIt you have the choice of

• installing the AutoIt environment and running the .au3 script file
• compiling a .exe from the script

In my experience you'll have far less trouble getting the exe approved by change control than requesting deployment of the entire AutoIt installation.

1. AutoIt

AutoIt v3 is the latest available version. The download contains a useful help file and example scripts. The web also has plenty of documentation and forum discussions on most topics you might be interested in. I'll just cover off a few points I thought were important:

• The language is a variant of Pascal
• Line continuation is performed with _
• Carriage return & line feed is represented by @CRLF
• Variable $CmdLine[0] contains the number of command line parameters provided
• ConsoleWrite() can be used to provide feedback and debug info
• The included Au3Info.exe is a handy GUI spy for identifying those much-needed CLASS and INSTANCE values
• Some functions are not suited to unattended use (see Q21 excerpt from AutoIt FAQ below)
• If your script is too fast, add some Sleep(n) statements to let the GUI catch up
• With the WinExists() function, AutoIt is able to handle GUI events that are too fast for a human to intercept or even perceive. Therefore writing a script based on manual user steps may be insufficient to properly control the GUI

Q21. Why my script doesn't work on locked station
A21. On locked station any window will never be active (active is only dialog with text "Press Ctrl+Alt+Del")In Windows locked state applications runs hidden (behind that visible dialog) and haven't focus and active status.
So generally don't use Send() MouseClick() WinActivate() WinWaitActive() WinActive() etc.Instead use ControlSend() ControlSetText() ControlClick() WinWait() WinExists() WinMenuSelectItem() etc.
This way you may have your script resistive against another active windows.and it's possibe to run such script from scheduler on locked Windows station.

2. SQL job

The SQL Server Agent is a service. By default, services do not interact with the desktop. This means your AutoIt exe can launch the utility but will be unable to detect or use any of its GUI elements.

To allow SQL Server Agent to interact with the desktop:

1. Start:Administrative Tools:Services
2. Right-click SQL Server Agent and select Properties
3. Select the Log On tab
4. Select Local System Account, check "Allow service to interact with desktop" and click OK
5. Restart SQL Server Agent (right-click and Restart)

Note the checkbox "Allow service to interact with desktop" is only available for the Local System Account. To use other accounts, they would need to be enabled to "Act as part of the operating system".

Potential problems are around whether Local System is suitable for running the agent service. Local System may not be suitable for other jobs on the same server. Some jobs may need permissions to other servers and it may not be appropriate to grant them to Local System.

A workaround would be to create a separate SQL Server Agent instance on the same server, just for jobs that need to interact with the desktop. However the additional instance will consume server resources.

A minor annoyance: a desktop-interaction-enabled SQL Server Agent will insist on keeping a Command Prompt window open while it's running.

SSIS - annoying animated data viewer creation

Why is there an animation when creating a data viewer in Visual Studio? Was Thwarted Game Dev Guy given the job of implementing this?

Mild irritation becomes serious annoyance when a live issue is being tested on a box under heavy load and the lil data viewers are taking a minute each to parachute into the data flow.

Copying SQL Server 2005 push replication using Generate Scripts

On the server where the existing replication is:

1. Right-click the publication (in SSMS, Replication: Local Publications) and select Generate Scripts...
2. Select To Create or Enable the Components
3. Choose file location and Save
4. OK and Close

On the server you want to copy it to:

1. If the server has never had replication before, manually create and delete a test publication (right-click Local Publications & select New Publication, choose some random stuff, then right-click the test publication and Delete). This will ensure the distribution database is created along with other stuff needed for replication
2. Open the script, replace the servers and logins as appropriate
3. Run on the target server

Using sp_msforeachtable on remote databases

Yes you can! The only proviso is that your linked server must have RPC and RPC Out enabled.

1. Right-click the Linked Server and select properties
2. Select Server Options
3. Click RPC & RPC Out and OK

Then you can do wild stuff like

exec [remoteserver].[remotedb].[dbo].sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"