Got Teradata working with SSIS 2008, pretty exciting and not very intuitive. Here it is. Environment Win 7, VS 2008 for SSIS, Teradata Client 13.x, Windows auth connections to Teradata work in our environment. I installed the Teradata client 13.x and it was working pretty well. Their SQL Assistant worked, I am a c# programmer and my c# calls for Teradata were working, so the Teradata client install was good. So I opened by VS 2008 SSIS environment and expected to see Teradata in the OLE DB Source options. Had a ton of microsoft drivers, finally 1 Oracle OLE DB driver (after trying a ton of different Oracle client installs) but no Teradata OLE DB driver, so what to do? Tried some things like SSIS Tools -> Connect to Database and got that to work. But a Teradata connection for OLE DB still wasn't showing. Then the key, I picked an ADO.Net Source. The ADO.Net source had a Teradata Database Data Provider option so now we are getting somewhere. You put in the dns name for your Teradata Server in SSIS Server Name and change the Mechanism on that page to LDAP. Then the tricky part. Click the "All" option in the upper left hand screen, in other places in SSIS this is the Advanced option. Here I kept Integrated Security as False but put in my userid and password where requested. Hit Test Connection and BOOM! the car was hot wired. Put in a File destination and I was getting all the columns out of Teradata. I have VS 2010 SSIS 2012 and would like to report how to connect to Teradata there but I don't have Teradata in that environment. Keep in mind not all of the infrastructure in your environment will be like my infrastructure.
Hi - Any instructions on doing this for Teradata 14.x? I tried to follow the instructions above, which were excellent and very clear and detailed, but they did not work for me. I'm guessing because I have a different version of Teradata. Any suggestions? Much appreciated!!
jgreenwood, I'm not sure if you're talking about td server 14.x or td 14.x client driver install. We don't have server 14.x in our environment yet so I can't help you there. Here were my results with td client 14.x. For SSIS I was on a ms sql 2008 install and hence SSIS 2008 on a win 2008 server machine with a brand new (2104-02) 14.x teradata client install. I did what I described above and it worked for SSIS access. I also want to report that a dot net c# compile with teradata ldap access compiled on a laptop with a td 13.x client ran on the ms sql 2008 server with td client 14.x .
One thing I would check if you are having problems with ssis connecting is what kind of authentication mechanism your teradata environment is using. The instructions above will only work for ldap. The other types I think are TD2 and SPNEGO. We poor slobs who are just users of huge corporate warehouses aren't always informed of what the authentication method is so sometimes you have to ask.
jgreenwood, TD2 is the native Teradata security. If it doesn't take too long just try what you did before but put TD2 for the Auth Mechanism rather than LDAP.
The best way to use SSIS with Teradata is through Microsoft Attunity connector. You can get this driver from this URl, and it will help to use TPT operators through SSIS.
I am getting the following error when executing my SSIS 2008 package on a windows 2008 R2 Server. I am using \Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC utility to run the package. Has anyone dealt with this before? I am using TTU 13.10, Microsoft Attunity Teradata Connector 1.2 for sql server.
There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. SqlState = IM003 Message = Specified driver could not be loaded due to system error 126: The specified module could not be found. (Teradata, E:\program files\teradata (x86)\Teradata\Client\13.10\ODBC Driver for Teradata\Lib\tdata32.dll).
I am getting the same error did you find any solution while i am using the teradata source
Error at Package2 [Connection manager "Teradata Connector 1"]: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. SqlState = IM003 Message = Specified driver could not be loaded due to system error 193: (Teradata, C:\Program Files (x86)\Teradata\Client\14.10\ODBC Driver for Teradata\Lib\tdata32.dll).
I am able to connect to teradata with ADO.Net source but not able to connect with attunity connectors