MS SSIS - OLEDB command

Third Party Software
Teradata Employee

MS SSIS - OLEDB command

Hi,

I try to load a Teradata table in Microsoft SQL Server 2008 Integration Services using OLE DB command transformation, but I have problems passing the column parameters of the Teradata table to the transformation.

In the OLE DB command transformation I use the following SQL command:

update p.resource set validTo=? where Id=?;

Because the system does not automatically recognize the parameters I entered the following external columns manually:

- Param_0:
DBParamInfoFlags=1
Datatype=DT_DBTIMESTAMP

- Param_1:
DBParamInfoFlags=1
Datatype=DT_STR
CodePage=1252
Length=36

The data types of the source columns are equal with the parameter data types above.
I also manually set the column mappings, but I get the following error message:

Unable to retrieve destination column descriptions from the parameters of the SQL command.

I tried a lot but I am not able to solve this problem. But I found out, that this problem only occurs when one of the parameters is a string (DT_STR). If I use a numeric parameter instead, everything works fine.

Unfortunately I have to use string parameters in certain situations, so avoiding strings is not a solution...

Is there maybe a better way to update Teradata tables in SSIS 2008?
Does anybody have experience updating Teradata tables in SSIS 2008 in general or with the OLE DB command in special?

Thanks in advance!

Best regards,
Helmut
4 REPLIES
Teradata Employee

Re: MS SSIS - OLEDB command

Yes there is a better way; in this blog

[url=http://developer.teradata.com/blog/netfx/2009/06/little-known-secret-about-microsoft-bi-tools-and-te...]

there is a link to "Microsoft Connectors for Teradata by Attunity". You can download it (free) from Microsoft but it requires Teradata TPT-API.

Also you can use ".NET Data Provider for Teradata" instead of "OLE DB".
.NET Data Provider for Teradata supports Batch Update and it works well with SSIS2008 as Data Source or Target.

Re: MS SSIS - OLEDB command

The SSIS connector seems to work great, but I'm getting an error when I try to run the package : "[Teradata Source [1]] Error: The Teradata TPT registry key cannot be opened. Verify that the TPT API 12.0 for Windows x64 is installed properly."

I have only been able to find a 32-bit version of the TPT API, but running a 64-bit OS, DBMS, Teradata drivers, etc. I'd like to continue with 64-bit across the board. The Teradata Corporation Front Desk sent me an email stating the 64-bit TPT API 12.0 does not exist. Is this possible? Do I need to remove the 64-bit drivers and replace them with 32-bit to get this to work?

Re: MS SSIS - OLEDB command

Even though this thread is more than 1 yr old, the link by glenn is mostly for Reporting Svcs so i am adding a method that worked for me.

If anyone is doing ETL in SSIS using teradata, here's how to get it working with dates (most difficult parameters to pass):

1. Either install the Teradata OLE DB drivers (available from TD downloads here - easier) or get the Attunity (Avail from MS downloads, also needs some components from TTU (teradata tools & utilities) which normally you dont find online since it ships with the teradata software.

2. Create StartDate and EndDate parameters as expressions and put in the expression (DT_DATE) GetDate(). Use GetDate() - 1 for EndDate. Create SelectString variable with a String datatype

3. Insert a Execute SQL Task. Add parameters with the variables above, set Datatype to DATE, Name to 0 and 1 respectively, size to -1 for both. Add parameter with SelectString variable, datatype NVARCHAR, Name to 2, parameter size to -1

4. In SQL statement, add the 2 variables:
DECLARE @StartDate char(10) = CONVERT(char(10),?,120)
DECLARE @EndDate char(10) = CONVERT(char(10),?,120)

5. Add your SQL to a string variable i.e.

SET ? = 'SELECT * FROM myTable where myDate between CAST(''''' + @StartDate + ''''' as DATE) AND CAST(''''' + @EndDate + ''''' as DATE)

6. Insert a Data Flow Task, add a OLE DB for Teradata source adapter, select the option - SQL from variable and use SelectString. Your TD query now returns records with dynamically passed dates!

- The reason why you would do that is because
1. SSIS date datatype is not the same as the SQL Server date datatype
2. You first do a cast to properly get the data into SQL Server date
3. you then pass it to teradata in a cast so TD can understand the date
4. This requires much less casting for other types of data
5. Only method i found to work without defaulting to Stored Procs and doing data manipulation there.

Re: MS SSIS - OLEDB command

Hi
I am looking for some help regarding connecting to a linked server in sql 2005 from teradata.
I am unable to se ethe oledb provider name so that the linked server can be called from the SSIS package.
My environment is 64 bit....
MS says they don't have any OLEDB provider for 64 bit.
ANY HELP.........??

URGENT?