SSIS Lookup transformation support for ADO .NET Connections

Question

Hi guys

I'm new in SQL Server Integration Services and I got a question:

Is it possible to use an ADO .NET connection in a lookup transformation of SQL Integration Services?

I just got 2 options in SSIS 2008: OLEDBConnection Manager and Cache Connection Manager.

I need it to be an ADO.NET connection since that's the only way to connect to SQL Azure databases (I've at least googled that).

 

If does not exist nothing like that... Does it exist a work aroud?

 

Thanks in advance. Warm Regards

Answer 1

I haven't tried to use an ADO.Net connection.

You could load a Cache Connection Manager using and ADO.Net Source.  In fact, if the data doesn't change much you could use a file as part of using a Cache Connection manager. 

http://blogs.msdn.com/b/mattm/archive/2008/11/22/lookup-using-the-cache-connection-manager.aspx

---

Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

Hi NTRLoneBattle,

please take Russ's solution as reference, and Lookup Transformation only supports OLE DB connection, someone had submited the similar issue to Microsoft Connect, please refer to: http://connect.microsoft.com/SQLServer/feedback/details/251562/ssis-lookup-tranformation-support-non-ole-db-connection

Thanks,
Eileen

Answer 3

Hi guys

Sorry for the delay...

I finally got the credentials and access to the sql azure database I had mentioned previously (the packages already worked fine in an on-premise basis)

I was a little bit worried about the oledb connections according to what I read on the internet...

but I ran a test and it worked fine :).

It was straightforward I just set up the oledb connection manager to point to the Azure DB and there you go.

 

Thanks for your time, Cheers