DB2 script to Remotely execute SSIS Packages

In preparation to allow DB2 job to remotely execute SSIS packages several configurations need to be put in place mostly on Windows side.

1.
    Create a .BAT file on the Windows servers and apply the following code. (Include the disk letter)
    Driver Letter:Call osql.exe -Q"execute msdb.dbo.sp_start_job [My SQL Agent Job]" 
     -S MySQLServer -U My SQL account -P My Password

2.
    Create a Windows account that has execute permissions to the .BAT file.

3.
    DB2 command to remotely execute the SSIS Package.

              RUNRMTCMD  CMD('I:\SSISJobs\ExecuteMySQLJob.BAT') +                                
              RMTLOCNAME('111.111.111.111' *IP) +                             
              RMTUSER('MyWindowsUser') RMTPWD('password') +                   
              CCSID(1252) 

    You will need to include the full path of the Batch file, IP address of the windows server, Windows      user account and password.

There are several other ISeries DB2 commands that can be used as an alternative to RUNRMTCMD

but this is sufficient to handle the task. 

4.
    Install the following IBM ISeries Client service. This needs to be enabled on the Windows Server.



5.
    Allow port 512 to be opened on the Windows server firewall.

Alter multiple Columns data types

This script was used in a staging environment to change all decimal and char columns to float and varchars.


Instructions are:

1. Run the script in SSMS.
2. Copy and paste the results in a new query pane and execute..

SSIS Windows Service Failing

I encountered this issue with the SSIS windows service failing.
It was generating these errors at each attempt to run the service.


The SQL Server Integration Services service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

....

A timeout was reached (30000 milliseconds) while waiting for the SQL Server Integration Services service to connect.


Log Name:      Application
Source:        Microsoft-Windows-User Profiles Service

Event ID:      1530
Task Category: None
Level:         Warning
Keywords:      Classic

Description:
Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards. 




There were several possible reasons suggested here
http://connect.microsoft.com/SQLServer/feedback/details/125768/sql-server-integration-services-service-failed-to-start-after-installing-sp1

but for this case it was resolved by adding this new entry in the config files mentioned here http://support.microsoft.com/kb/918644 Method 1

    <runtime>       
    <generatePublisherEvidence enabled="false"/>   
    </runtime>

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
    msdtssrvr.exe.config
    DTExec.exe.config
    Dtshost.exe.config
    DtsDebugHost.exe.config
    DTUtil.exe.config

SQL Server Finding Timeouts

You need to create a table that contains your trace data.

You can import this from your .trc file using
Or if you used Sql Profiler save the trace data File -> Save As -> Trace Table

Running the following command you can identify the process that had not completed.

SQL Server 2008 R2 Install Complete Management Tools

To install the Complete  Managament Tools on an existing Sql Server 2008 R2 run the following command.

setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=MyInstanceName /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\Local System” /IACCEPTSQLSERVERLICENSETERMS

Sql Server Script AutoFix Orphaned Users

A common task I'm asked to perform by developers\testers is the copying of production databases to dev or test environments.
The backup and restoring in batches of databases can have the affect of creating the orphaned users, where the database accounts become misaligned with the Sql Server accounts.

The general resolve is to use the sp_change_users_login proc. The issue arises when there are multiples of accounts to fix  along with several databases. This can be a time consuming process to do this manually for each account.

The following script I wrote helps reduce the time involved in fixing the orphaned accounts.

SQL Server 2008 R2 Restore Script in Batches