none
SSIS Datareader Source SQL command errors RRS feed

  • Question

  • Hi,

    I have been using SSIS datareader source component to get data from SQL server using command "execute stored_procedure_name param1, param2". where stored_procedure_name is a user defined SQL stored procedure and param 1 and param 2 are input parameters to the SP(stored procedure). stored_procedure_name consists of hash tables in it. The SQL command works fine where run in SQL server. however the same query shows up error at datareader source. the error looks like "[DataReader Source [1]] Error: An error occurred executing the provided SQL command. " "[DTS.Pipeline] Error: "component "DataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN".  it shows the hash tables as invalid. could you please help me out with this issue?

    Tuesday, February 23, 2016 12:43 PM

All replies

  • Hi RasMath,

    The issue may occur due to various factors. Here the conditions that you can considerate:

    • The user CORP\fcispacluster (may be the SQL Server Agent Service Account or a SQL Server Agent Proxy Account) doesn’t have sufficient permissions on the Teredata database,
    • It uses 32-bit driver for Teredata data source in BIDS but the SSIS server natively tries to use 64-bit driver that is not installed.
    • The protection level of the package is set to EncryptSensitiveWithUserKey, however, the SQL Server Agent job that runs the SSIS package is not executed by the same user, therefore, cannot decrypt the sensitive data correctly. In this condition, we can use the EncryptSensitiveWithPassword and specify the password (/P XXXX) in the command line in the job step or set the protection level of the package to “Rely on server storage and roles for access control”.

    For the detailed steps, please see:
    http://social.technet.microsoft.com/Forums/sqlserver/en-US/e13c137c-1535-4475-8c2f-c7e6e7d125fc/how-do-i-troubleshoot-ssis-packages-failed-execution-in-a-sql-agent-job?forum=sqlintegrationservices.

    Regards,

    Tuesday, February 23, 2016 2:20 PM
  • " "[DTS.Pipeline] Error: "component "DataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN".  

    Check you "DataReader Source" (1). Where do you connect to? Is your credential to the data source correct?

    For example:

    If you source is SQL Server then make sure your SQL Server Management Studio can access to that server.

    If you source is Excel then make sure you have the permission to the folder and so.

    chanmm


    chanmm

    Saturday, February 27, 2016 1:36 AM