How do I add an ODBC connection data source as a Data Flow Source

I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.

Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?

Thanks in advance

ADG

Use DataReader Source for ODBC connections.|||

How do you know that the slowness is in the OLE DB sauce component and not in the destination component? How are you setting up the OLE Source component? Providing a query would be faster than selecting a table from the dropdown list.

In general I would not expect ODBC to be faster than OLE DB. but you can test it; create 2 different dataflows 1 with datareader(ODBC) and other with OLEDB (provide a query!) and then connect it to a rowcount or union all transform. When you run the packages you will be able to tell how long it takes to 'read' the data.

how come the validation doesn't seem to end keep on running?

Hi folks,

it is me again. Just want to enquire how come with the control flow of the Data flows been set appropriately, why when it is executed manually in the sql server 2005 file system, the validation shows the incorrect flow of data flows been executed and doesn't seem to end? Thanks in advance

Regards,

Ken

Hi Ken,

It's probably just me combined with the fact I've had no coffee yet, but I don't follow. Can you restate the issue?

Thanks,
Andy

|||

Hi Andy,

Thanks again for helping this newbie. Ok I have input my package into the SQL Server 2005 file system located under Stored Packages. I run them manually which displays a validation report on its progress. But i notice from the validation report that it doesn't run the validation check as how I drew the flow of the components and it seems to be running endlessly. Is there a way to edit the package so that it can signal the end of the package.

|||

Hi Ken,

You are most welcome - I was a newbie myself not long ago.

If I'm understanding you correctly, either your package is stuck in Validation (or appears to be) or stuck in execution (or appears to be).

If the package is stuck in validation, you can change the Package level property DelayValidation to True. The package will load and attempt to run. Any errors will likley cause it to crash, but it may give you insight into why it is behaving in this fashion.

If the package is stuck in execution, I suggest you implement logging. Also consider custom logging using Execute SQL Tasks on the Control Flow and in OnError event handlers.

Hope this helps,

Andy

|||Are you executing child packages from a parent package, by any chance?

How can pass variable or parameter in DATA READER SOURCE ising ADO:NET Connection manager

In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .

I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .

Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...

Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa

USe an expression to override SQLCommand porperty of the data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1283829&SiteID=1