Problem when a IS tries to move data between servers

Hello,

I have a problem: I have created an Integration Services in SQL Server 2005 that moves data from a table in a server to another table in another server. I have set the protection level property to "don't save sensitive".

The problem is when I try to execute it with a dtexec command. I get this errors:

'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxx'.".'

I don't know why the Integration Services does not work, and what are this errors about. Maybe the problem is with the protection level property, because when I set it to "don't save sensitive" it has some warnings, but when I set the property to "Encrypt sensitive with user key" the error messages are the same.

Thanks,

Pablo Orte

Pablo,

What you are seeing, I think, it's an expected behavior. When you choose 'Don't save sensitive' NO sensitive info is saved; and that includes the password in connection strings. See here for more details: http://msdn2.microsoft.com/en-us/library/ms141747.aspx

You could avoid this situation on several ways; I personally use 'Don't save sensitive' and then assign connection strings at run time using 'package configurations' as described in the method 4 of this article: http://support.microsoft.com/kb/918760

Notice thta if you are using SQL Server database you could use Windows authentication that does not require explicitly saved pasword as a part of the connection string.

|||

Hi Rafael,

Thanks for your reply. We have an Scrypt Task at the start of the IS, that assign a connection string to the different servers. The values of the connection strings (user, password, server, and database) are passed as variables.

The thing is that when we execute the IS in the SQL Server Business Intelligence, It works fine and does everything ok. But when it is executed with the dtexec command it gives us that error.

Thanks,

Pablo Orte

|||

Using script task for this kind of task is something I have never done; Package configuration is the natural way to me to solve it. Is there something that prevents you to use them?

I am not sure, but your problem may be related with the order on which things occur when running the package; if I recall correctly package configuration go first; then the connection managers. Saying this, I don't know if by the time you script task attempts to replace the connection strings it's already too late.

BTW, when you say you are passing user/password/server via variables; are the values local in the package or they are received via package configuration?

|||

Well, the fist thing our IS do is setting the connection string. We do this with variables because our ISs do things in different servers, at different times. I mean, we have some servers, and we use the same IS to do things with them.

After this, our IS truncate a table and the call a data flow that do a select instruction from a server, convert the data a insert it into another server. Then, it truncates another table and finish. We think that this error may occur because the dataflow between servers is wrong or it doesn't set the connection string properly.

The variables are passed with the dtexec command and they are variables in the package.