locked
Datareader Source configuration RRS feed

  • Question

  •  

    created a datareader source based on ODBC to MOMS database, when execute task, get error below, not sure what configuration should be done?

    Error: 0xC0047017 at star, DTS.Pipeline: component "Read from Star" (218) failed validation and returned error code 0x80131937.

    Tuesday, January 10, 2012 8:54 PM

Answers

  • I see now you use a Data Reader as the destination- this is the culprit.

    Use the OLEDB destination instead.


    Arthur My Blog
    • Marked as answer by George Zhou Thursday, January 12, 2012 3:22 PM
    Wednesday, January 11, 2012 3:31 PM

All replies

  • Hello George,

    How did you ensure the ODBC connection works?

    Can you also post a picture of what you created?


    Arthur My Blog
    Tuesday, January 10, 2012 8:56 PM
  • Hi Arthur, I can use the ODBC in my web program to retrieve info so I believe the ODBC works. also the test connection in SSIS works.

     

    • Edited by George Zhou Wednesday, January 11, 2012 2:35 PM
    Wednesday, January 11, 2012 2:34 PM
  • Ok, your connection is OK, but what are you doing inside the control? What SELECT statement is? You can use a viewer to see data getting from Source control
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 11, 2012 2:46 PM
  • What kind of a task you are referring to in your opening statement?

    Do you use a config file (thinking there is chance for some sort of mis-configuration)?


    Arthur My Blog
    Wednesday, January 11, 2012 3:00 PM
  • Thanks both, the select statement is very simple for testing: SELECT PHY_NAME, PHY_LOC, PHY_PHONE FROM AG_PHYSICIAN where phy_name like 'AZ%'

    I am selecting the 3 columns around 20 records in a Data Reader Source, try to export the records in a table on a database. Not sure how to correctly set up DataReader Destination, when tried execute task, it has the error showing red on DataReader Source.

    Wednesday, January 11, 2012 3:27 PM
  • I see now you use a Data Reader as the destination- this is the culprit.

    Use the OLEDB destination instead.


    Arthur My Blog
    • Marked as answer by George Zhou Thursday, January 12, 2012 3:22 PM
    Wednesday, January 11, 2012 3:31 PM
  • Thanks Arthur, that helps(voted) configuration.

    The issues looks like is the driver issue, but followed the url http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362(v=vs.85).aspx, using 32bit configure it, still not fix the error.


    SSIS package "Package.dtsx" starting.

    Information: 0x4004300A at star, DTS.Pipeline: Validation phase is beginning.

    Error: 0xC0047062 at star, DataReader Source [559]: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

    at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)

    at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)

    at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

    at System.Data.Odbc.OdbcConnection.Open()

    at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)

    at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)

    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

    Error: 0xC0047017 at star, DTS.Pipeline: component "DataReader Source" (559) failed validation and returned error code 0x80131937.

    Error: 0xC004700C at star, DTS.Pipeline: One or more component failed validation.

    Error: 0xC0024107 at star: There were errors during task validation.

    SSIS package "Package.dtsx" finished: Success.


    • Edited by George Zhou Wednesday, January 11, 2012 4:26 PM
    Wednesday, January 11, 2012 4:25 PM
  • Try to test this package from another machine. If you cannot reboot all you can.
    Arthur My Blog
    Wednesday, January 11, 2012 4:38 PM
  • tried another machine with SSIS 2008, it can view tables, but cannot process queries, got this:

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at Data Flow Task [ADO NET Source [1]]: Component is already connected. The component needs to be disconnected before attempting to connect it.

     

    ------------------------------
    ADDITIONAL INFORMATION:

    Pipeline component has returned HRESULT error code 0xC020200A from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    tried to use OLEDB destination, there is no column to map

    but the source has the columns:

    • Edited by George Zhou Wednesday, January 11, 2012 7:57 PM
    Wednesday, January 11, 2012 7:07 PM
  • Replace the DataReader with OLEDB, I even do not know why you used in the first place
    Arthur My Blog
    • Proposed as answer by Eileen Zhao Thursday, January 12, 2012 2:38 PM
    Wednesday, January 11, 2012 7:31 PM
  • Hi Arthur, the OLEDB doesn't show the data source, there is no driver for OLEDB to connect to that database. Anyway, we have figured it out now, Datareader source to OLEDB destination.
    Thursday, January 12, 2012 3:22 PM