I'm trying to get the .net provider for teradata working. Following sumerized the situation:
1) If I put the ip address in the connection manager then I it test OK within the connection manager but gets "Failed to acquire connection... Connection may not be configured correctly" when I execute the SQL task.
2) If I put the name for the server in I get the "MTDP:EM_NOHOST(224) error from teradata. The host name is 5 characters and is in the hosts file and is pingable via the name.
3) When I try to connect with nquery I get the CLI error 510 when I try to connect. Can't find what this means anywhere.
4) Other ado.net providers are working
I've been searching and searching and have found lots of info by nothing that solves my problem
1) Created a table import that just copied a table from teradata. It worked and I was able to save it as an SSIS package - so I have an SSIS package that works.
2) However I can't create an SSIS package directly that works. I've tried to duplicate the working import package without success. Checked every setting I can find and they are the same. Getting a new error though. I know get the same initializing CLI error 510 that I was getting with nquery.
Based on your info of what the 510 error meant, I uninstalled everything (gss, icu, cli) and re-installed the versions that came with the .net drivers rather than some of the newer versions that were on the teranova web site which I had used before. All is working thanks.
I can't tell you what that error 510 means, but it sounds like you could be using 32-bit drivers in a 64-bit environment. That would allow it to work in your development environment but not under SSIS.
Try executing the package using the 32-bit DTExec. The command line is usually this: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"
To get the parameters you'd need to pass to this, create a SQL Agent Job, then create a Job Step of type "SQL Server Integration Services Package". Set all the settings to what you'd like, then click on the Command Line tab.
Copy the command line from there, re-adding any passwords if they need to be added.
Cancel that step creation, and create a new one that's an Operating System (CmdExec)
Paste in the Command Line: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\My Test Package" /SERVER DEVSQL01 /MAXCONCURRENT "-1" /CHECKPOINTING OFF /REPORTING E