Acquire Connection Error in SSIS

I got this error in SSIS:

" [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DW.PPC2" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed."

I was not able to run the package, but I was still able to preview records in that OLEDB Data Source (in Visual Studio).

I was unable to find anything tangible in BOL, log or Google. Finally, I want to the server and realized that Named Pipes are disabled. I created an alias that forces connection to use TCP connection to the database server and my problem was solved.

Put Expressions in Variables

Instead of putting expressions in object properties, try putting them in variables. That will allow you to debug them (in Watch window) and to reuse them for other objects.

(Expressions are features of SSIS packages that allow you to assemble values of properties at run time of dtsx / SSIS package. They are replacment for Disconected Edit in DTS 2000. Expressions are set in Visual Studio when you start editing individual package. )

Long Validation of SSIS Packages in Visual Studio

One of the irritating things regarding creation of SSIS packages is that Visual Studio (or Business Intelligence Development Studio) can take couple of seconds (or even couple of minutes) to validate .dtsx packages against data sources (databases). This is annoying when you are opening a single package (from Solution Explorer), but it is really frustrating when it happens during opening of a Integration Services project that contains multiple packages. Visual Studio will validate each open package.

Solution is to close all opened package before closing Visual Studio.

Managing files in Script Tasks

Script Task in SSIS can be used to manage files on a server. I will give just simple examples here. Some of them can be performed (even simpler) using different methods like File System Task or batch files, but scripts are really useful when you have to do something more complex. Example of that would be when location of files are dynamic (typically different server in different environments).

Classes for managing files are in System.IO namespace. You could add that in the header of the class and then you do not have to reference that, but to make things obvious, I will repeat System.IO even when it's redundant.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim sFileA As String

'Read location of the file from connection string
sFileA = Dts.Connections("userLog").ConnectionString
Dts.Variables("userLog").Value = sFileA
'debug MsgBox(Dts.Variables("file_userLogToLoad").Value.ToString())

sFileB = "F:\Folder1\userlog1.txt"
sFileC = "F:\Folder2\userlog2.txt"

'check if file A exists
If System.IO.File.Exists(sFileA) Then
' check if file B exists
If System.IO.File.Exists(sFileB) Then
'Copy a file (A -> C)
System.IO.File.Copy(sFileA, sFileC)

'append C with B
Dim s As String
s = System.IO.File.ReadAllText(sFileB)
System.IO.File.AppendAllText(sFileC, s)

'' To Delete userlog file for next user log load
System.IO.File.Delete(sFileA)

''' To rename/Move a file
System.IO.File.Move(sFileC, sFileA)
End If
Dts.Variables("TruncateWorkTable").Value = 1
End If
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Copy file on network drive with(out) UNC path using Cmd file

SSIS has tasks for managing files. However, sometimes it is easier to perform some operations in a batch (.bat or .cmd) file.
Some of commands in it (like Copy) have roots in DOS, so they do not support more modern options like UNC paths (just drive pats).
The following code defines drive that points to UNC path. It is also an opportunity to specify login and password needed to access remote folder (otherwise everything will be executed in context of current user).

Rem Define Drive for target UNC path
net use L: \\Server.Domen\SharedPath /user:Domen\User Password

Rem Perform operation
copy L:\SubFolder\original.txt F:\Folder\SubFolder\Destination.txt

Rem Wait for process to finish
sleep 30

Rem Remove Drive
net use L: /delete

SSIS Indirect Configurations

One thing that is not clear in BOL and many web articles about Indirect Configurations is how to do it.

1. Create configuration directly (in a dtsConfig file on the local disk) using Package Configuration Organizer (SSIS > Package Configuration…).
2. Create environment variable that points to the file (you can use Set command in cmd.exe window)
3. Restart Visual Studio or you may not see the variable in VS. In some cases you may even need to re-login .
4. Open the Package.
5. Reopen Package Configuration Organizer and edit the configuration.
6. Change it to Configuration location is stored in an environment variable (see Figure) and choose Environment variable from the list. Then press Next, save everything…

If only we could change the folder in multiple environment variables in one quick change.

SSIS Convert Indirect Configuration to Direct Configuration

If you switch from Indirect to direct configuration of the package, you will lose dtsConfig file. The proper way to do is to drop Indirect configuration and then Add direct (you will be prompted to reuse existing file).

Setting Environment Variables

A quick way to set environment variables is to add/edit them in registry. They are in following node:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

I don't think that you should use user environment variables.

HKEY_CURRENT_USER\Environment

If you need to change a set of variables, simply create a .reg file with content such as:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]

"dtsConfig_GeoLog"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\GeoLog.dtsConfig"

"dtsConfig_DW-PPC2"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-PPC2.dtsConfig"

"dtsConfig_OLTP-PPC"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\OLTP-PPC.dtsConfig"

"dtsConfig_SMTP"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\SMTP.dtsConfig"

"dtsConfig_MailToFrom"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\MailToFromdtsConfig"

"dtsConfig_DW-Financials"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-Financials.dtsConfig"

You can create this type of file by manually editing nodes in RegEdit and then by Exporting them.

Note: After you create these variables, they may not immediately become available to you. You may need to restart process (Visual Studio) or even re-login.

Simple way to set dtsConfig - SQL Agent step

There is a much simpler way to set location of configuration files then Indirect Configuration - through SQL Agent jobs:

Intro

This blog is dedicated to development and management of SSIS (or DTSX) packages in SQL Server 2008 and SQL Server 2005.