Post reply

Stored procedure fails when executing with SSIS package :"Communication link failure"

  • May 19, 2008 at 9:13 am

    #154364

    Hi all,

    I am wondering if you guys have any experience with failing Stored Procedures running inside a SSIS package with the following error:

    =====================================================

    Message

    Executed as user: GAALPSVR034F\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:51:22 AM Error: 2008-05-19 09:06:55.15 Code: 0x00000000 Source: usp_SLIM_Site_PreProcess Description: TCP Provider: The specified network name is no longer available. End Error Error: 2008-05-19 09:06:55.18 Code: 0xC002F210 Source: usp_SLIM_Site_PreProcess Execute SQL Task Description: Executing the query "Exec usp_SLIM_Site_PreProcess" failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:51:22 AM Finished: 9:06:55 AM Elapsed: 932.203 seconds. The package execution failed. The step failed.

    ======================================================

    Sometime it fails when gets called through a job and sometimes even when a package is open in a design mode. The actual Stored Procedure NEVER fails if called in SQL Server Management Studio.

    Any ideas or suggestions will be very helpful and appreciated

    Thanks for your help!

    Jacob


    Jacob Milter

  • May 19, 2008 at 10:20 am

    #817293

    I guess it is the problem with the network so the communication error.

    -V

  • May 19, 2008 at 10:24 am

    #817297

    THanks for the answer, but it runs fine if outside SSIS!


    Jacob Milter

  • May 19, 2008 at 10:28 am

    #817298

    Can you provide more information regarding your SSIS package? How are you executing the Sp in SSIS?

    😎

  • May 19, 2008 at 10:37 am

    #817300

    Lynn,

    Thanks for your reply.

    I am executing through the Execute SQL object:

    Exec usp_SLIM_Site_PreProcess

    Very simple code!

    it runs fine outside BIDS!


    Jacob Milter

  • May 19, 2008 at 10:40 am

    #817304

    Lynn,

    One more thing:

    Sometimes it runs fine even as a job!

    But when it fails the only way to rerun is the query editor.

    THanks again

    Jacob


    Jacob Milter

  • May 19, 2008 at 10:48 am

    #817307

    Okay, so you are using an Execute SQL task; that doesn't fully answer my question regarding your SSIS package. How is your package setup? What else is in your package?

    😎

  • May 19, 2008 at 10:50 am

    #817309

    This part of your post is critical in determining what is wrong:

    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned

    😎

  • May 19, 2008 at 10:52 am

    #817311

    Lynn,

    You lost me there...:)

    This package contains 7 other Execute SQL tasks...

    Could you pleas elaborate your question?

    THanks for your time!


    Jacob Milter

  • May 19, 2008 at 11:05 am

    #817324

    Lynn,

    Got it!

    ResultSetType Property - ResultSetType_None

    Connection Property - DEV12.biaatl.us.ups.com.SLIM_Develop.bia_db_sql_id (valid name that we use work perfectly!)

    Parameter Mapping - No parameters are needed.

    Thanks again for your time and effort

    Jacob


    Jacob Milter

  • May 19, 2008 at 11:53 am

    #817354

    Do the other Execute SQL tasks work okay?

  • May 19, 2008 at 12:04 pm

    #817364

    Lynn,

    Yes! And the properties are the same.

    THanks again

    Jacob


    Jacob Milter

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply