Data Reader Source as ODBC / complex sql

Hello All,

How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does

Create newtable as

(select day, sessionId)

from source

// lots of other joins and unions

select day, sessionId from newtable

drop newtable

I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?

Are there any good examples of this, taking data from a ODBC source into SQL server?

Thanks in advance.

Because your first statement does not return a resultset, I'd guess that SSIS is not able to interpret the metadata to create the output columns for you. Try adding a dummy select as the first statement.|||

Any special reason for no breaking the logic accross diffrent components/tasks?

I would use an execute sql task in control flow for the create and the drop table; that way the data reader would have only the query.

|||I don't understand. Why even do the create table and drop table statements? Why not just use the query you used to populate the table?|||I've never worked on a Netezza data source, but I have seen similar patterns from people who have. Maybe it's a more optimized approach for the Netezza engine. However, I don't really know - maybe the OP can clarify. I'd like to know just for my own personal education.|||

I am jumping in on the project 1/2 through. All of the querying the ODBC source (netezza) was done before my time. My job is to just convert our current process to SSIS. I am having a lot of problems doing this

- I created in a execute sql task and create a temp table

- The next step is a data flow task

- The source is a DataReader Source (this is were the problems start)

In trying to get this to work I did the following to the Netezza connection string:

- RetainSameConnection = True

- DelayValidation = True

Then I would get this error

Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.

at System.Data.Odbc.OdbcConnection.SetStateExecuting(String method, OdbcTransaction transaction)

at System.Data.Odbc.OdbcCommand.ValidateConnectionAndTransaction(String method)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131509.

I would like to know how to get this to work, however after a day to think about the design I am going to create view which will replace the create temp table in step 1 (this will be done offline and not in the SSIS package). Now my query can use that instead creating the temp table.

Thanks for everyone's help, I really wish there were more examples of querying ODBC source and entering the data into SQL Server. I thought that would be one of the most common uses of SSIS.