Category Archives: SSIS

SQL Server Integration Services

Replicate logins and jobs in #SQLServer #AlwaysOn environments

After working for a while with SQl 2012 and AlwaysOn it becomes clear that keeping logins, jobs and other uncontained objects in sync between different hosts is a problem.

Maybe not if your running a small cluster with only 2 servers and one or two AG’s, but when you have 5 servers and 5 AG’s it becomes a hazzel to keep control of what should be copied to each server.
I started out with home made script and synclogin scripts but it feels like I’m always forgetting something.

Today I found this:

SQLSkills and Jonathan Kehayias have created a SSMS add-in that lets you create SQLCMD script that can copy everything. Easy to use and a very good start.

So far it only creates a SQLCMD script that you will have to automate yourself, but Jonathan believes they will continue to develope it into a more automated utility.

That sounds very interesting.


Querying Active Directory from SQL

Here is a very good article about how to query Active Directory from within SQL.
It describes both directly through a linked server with it’s limitations on number of returned rows.
But it also describes another method using CLR.

SSIS Unexpexcted termination when running multiple packages in parallel

An escalating problem at a customer site is a problem with SSIS when running multiple SSIS packages in parallel.
We get the following error on intermittent occasions, sometimes after just a couple of minutes, sometimes after 2-3 hours of running.

We have applied the latest SQL 2012 CU3 patch and also a hotfix from Microsoft
But the problem persists. We get the following error in the Windows application log:

Faulting application name: ISServerExec.exe, version: 11.0.3349.0, time stamp: 0x513a9017
Faulting module name: ntdll.dll, version: 6.1.7601.17725, time stamp: 0x4ec4aa8e
Exception code: 0xc0000374
Fault offset: 0x00000000000c40f2
Faulting process id: 0x1fe0
Faulting application start time: 0x01ce4ffb609c5176
Faulting application path: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISServerExec.exe
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
Report Id: 6b101cc8-bc06-11e2-8a88-e41f13b85634

Yesterday we finally found a blog post where someone had gotten rid of this problem by adding the SQL agent account to the local admin group on
the server. They where not able to figure out what permission exactly was missing. In our case and on the first server where I tried this,
the same account was used for both the SQL engine and the agent. It’s not good practice to give the sql account local admin permissions but in
this case it’s the only solution I have found that seems to be working.

And guess what, it seems to have worked !!!

At least last night…. But this error is very intermittent and I will let it run for at least a week
before I consider this a working solution.

We will eagerly be awaiting Cumulative Update 4 which is due to be released this week and that might include a fix for this.

I’ll continue to work on the permission clue and see if something more can be found and the I will update this post

Update 2013-05-24

Giving the SQL services accounts local admin permission didn’t help…
Applied a bunch of Windows .Net patches yesterday. See if that helps.