1

I'm working on modifying an SSIS package that includes several lookup transformations. In my experience you must have an OLEDB connection created to use a lookup. I am being asked to change the lookup to connect to a Teradata connection. We have installed the Attunity drivers and Teradata is an option under ADO.NET connections but not under native oledb connections. For reference I see the native oledb for Oracle. How do I create a connection to Teradata that I can use in a lookup or an ole db command?

Nathan J.
  • 13
  • 2
  • 5

2 Answers2

2

For 2008+, you have two options for a Lookup Component. The first is to use an OLE DB Connection Manager. For all other Connection Manager types, use a Cache Connection Manager

Add a Data Flow, I usually call mine "Warm Cache" where you use your source, an ADO.NET Source and route it to a Cache Transformation destination. When your package begins, it will dump all the data to the CCM.

Then, in your "actual" data flow, change the Connection type to reference the Cache you populated in the previous step.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • We are using 2008R2. This sounds like a good fall back option should the driver above not work for us and I appreciate your response Bill! – Nathan J. Feb 09 '15 at 23:07
  • This works great for our lookups however now we need a solution for replacing our ole db commands inside the data flow. Thinking we need to store the results of our lookup in a work table and then use an execute SQL task outside the data flow to accomplish this. Any other ideas? – Nathan J. Feb 12 '15 at 19:39
  • The OLE DB Command works great for 10s of rows to be updated. Anything more than that, you're usually better off staging all of the updates that need made to a table and then performing a set based update afterwards, much as you're indicating. Bingle "SQL Server Incremental Load Pattern" by Andy Leonard. He's got a great series on SQL Server Central, free login required, that covers all of this. – billinkc Feb 12 '15 at 19:44
  • Unless there's some weird Teradata specific reason that doesn't work. Never touched it – billinkc Feb 12 '15 at 19:45
1

As for the Teradata driver, you can download the OLEDB driver from Teradata, available here

Andrew
  • 8,445
  • 3
  • 28
  • 46
  • Thanks Andrew. So installing this driver will allow me to choose Teradata from the provider dropdown when configuring a new OLE DB connection manager in SSIS 2008R2? – Nathan J. Feb 09 '15 at 23:04
  • After the install we are able to choose an ole db connection to Teradata however when we attempt to click okay after configuring the lookup or ole db command SSIS hangs. Additionally we continually got an error about not being able to get field descriptions from teradata in an ole db command. – Nathan J. Feb 12 '15 at 19:36