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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics