Monday, January 16, 2012 10:58 PM
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
Monday, January 16, 2012 11:06 PM
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.
Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
Wednesday, January 18, 2012 3:45 PMModeratorHi 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
Wednesday, May 02, 2012 5:23 PM
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
- Marked As Answer by NTRLoneBattle Wednesday, May 02, 2012 5:25 PM