TimeOut issues : SSIS service on different server than SQL Server service
Hello,
Here's my configuration :
Server_A :
SQL Server service started
Agent started
"Remote Query Timeout" = 0
Server_B :
SSIS service started
MsDtsSrvr.ini.xml edit to point to MSDB on Server_A
Both servers run SQL Server 2005 Enterprise Edition (SP2) on Windows Server 2003 R2 Standard SP2.
Everything run under a domain account (Windows Authentication) with admin rights on both servers.
No firewalls are running and I addmy user to msdtsserver in DCOM in component services.
I use a C# application (Winform) to create a folder on SSIS and then import packages under this new folder.
Here's the piece of C# code :
Code Snippet
string p = System.Windows.Forms.Application.StartupPath + @"\ETL\" + info.Name;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Boolean folderExists = app.FolderExistsOnSqlServer("MyPkgFolder_" + _conf.DbSource + "", _conf.Ssis, null, null);
if (folderExists == false)
{
app.CreateFolderOnSqlServer("\\", "MyPkgFolder_" + _conf.DbSource + "", _conf.Ssis, null, null);
}
if (info.Directory.Name == "ETL")
{
// Refresh packagesGUID
DirectoryInfo di = new DirectoryInfo(System.Windows.Forms.Application.StartupPath + @"\ETL");
FileInfo[] rgFiles = di.GetFiles("*.dtsx");
foreach (FileInfo packageFile in rgFiles)
{
ProcessStartInfo infos = new ProcessStartInfo("dtutil.exe", "/i /File \"" + packageFile.Name + "\"");
infos.WindowStyle = ProcessWindowStyle.Hidden;
Process proc = Process.Start(infos);
}
// Load& save packages.
Package pkg = app.LoadPackage(p, null);
app.SaveToSqlServerAs(pkg, null, "\\MyPkgFolder_" + _conf.DbSource + "\\" + info.Name.Substring(0, info.Name.Length - 5), _conf.Ssis, null, null);
}
Everything works fine when tested on a single server where both SQL Server and SSIS services were running.
Now with Server_A and Server_B the following error is raised :
Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).---> System.Runtime.InteropServices.COMException (0xC001404A): While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).
I checked event viewer on both servers but everything is listed as fine.
Does anyone have any clues to help me to resolve this issue ?
Thanks.
Regards.
Bertrand
September 10th, 2008 10:29am
I get the same error, from another angle: Trying to upload SSIS packages to a remote server using a batch file calling dtutil statements. It works like a charm to deploy SSIS packages to a remote SQL2005 server. When using the appropriate new dtutil and
new ssis setup to deploy to a remote sql2008 server, i get the same error combo : <<< Error (0xC001404A) while checking for the existence of folder on SQL Server. Description: While trying to find a folder on SQL an OLE DB error was encountered with error
code 0x80004005 (Login timeout expired). >>> Strangely, I can add the package from my local drive using sql management studio, from my logon. Did you ever manage to fix your issue ? Many thanks, Peter
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2011 6:31am
rem echo on
cd D:\Program Files\Microsoft SQL Server\100\DTS\Binn
set svr=GFOPSDGT001
set src_fld=D:\GA\DWH\SSIS\2008\deploy\
set dest_fld=DWH
for %%f in (%src_fld%*.dtsx) do dtutil /Q /Fi %%f /C SQL;%dest_fld%/%%~nf /DestS %svr%
PAUSE
March 23rd, 2011 6:35am
It seems like a security issue.
On which server does the SSIS package run? Is it scheduled within a job? If yes, on which server is this job located?
Does the job use a proxy or the SQL Server Agent account? Which account uses the Agent?
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2011 6:44am
thanks koen, I found that it was related to having a named instance on the server
set svr=GFOPSDGT001\FOPS
fixed it.
I got confused because SSIS is accessed directly (it doesn't support multi-instance), hence you log onto GFOPSDGT001 through sql management studio, yet for dtutil you are accessing msdb database on the instance hence you need to refer to that.
Thanks again, Peter
March 23rd, 2011 6:51am
Ah yes, SSIS and named instances :)
I hope they get rid of that issue with Denali.
Free Windows Admin Tool Kit Click here and download it now
March 23rd, 2011 7:10am