I'm executing a package via .NET (example 1, example 2), but if the packages takes more than 30 seconds I get an error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Changing the timeout in the connection string to for example 300 has no effect.
// C# Code (incorrect) // Connection to the database server where the packages are located using (SqlConnection ssisConnection = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Connection Timeout=300") { try { // SSIS server object with connection IntegrationServices ssisServer = new IntegrationServices(ssisConnection); // The reference to the package which you want to execute PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["SSISJoost"].Projects["MyProject"].Packages["MyPackage.dtsx"]; // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>(); executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 }); // Add a package parameter executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "myStringParam", ParameterValue = "some value" }); // Get the identifier of the execution to get the log long executionIdentifier = ssisPackage.Execute(false, null, executionParameter); } catch (exception ex) { // Log code for exceptions } }
Solution
The 30 seconds is the default timeout which apparently can't be changed in the ssisPackage.Execute command. The solution is a little dirty. First remove the SYNCHRONIZED parameter to execute the package asynchronized. Then add some code after the ssisPackage.Execute command.
// C# Code (correct) using (SqlConnection ssisConnection = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=SSPI;")) { try { // SSIS server object with connection IntegrationServices ssisServer = new IntegrationServices(ssisConnection); // The reference to the package which you want to execute PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["SSISJoost"].Projects["MyProject"].Packages["MyPackage.dtsx"]; // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>(); //executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 }); // Add a package parameter executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "myStringParam", ParameterValue = "some value" }); // Get the identifier of the execution to get the log long executionIdentifier = ssisPackage.Execute(false, null, executionParameter); // Get execution details with the executionIdentifier from the previous step ExecutionOperation executionOperation = ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier]; // Workaround for 30 second timeout: // Loop while the execution is not completed while (!(executionOperation.Completed)) { // Refresh execution info executionOperation.Refresh(); // Wait 5 seconds before refreshing (we don't want to stress the server) System.Threading.Thread.Sleep(5000); } } catch (exception ex) { // Log code for exceptions } }Thanks to SequelMate and HansAnderss
No comments:
Post a Comment
Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.
All comments are moderated manually to prevent spam.