SSIS Teradata connect

SQL Server integration services  2008 provides 3 new connectivity options . The three new connectors (for Oracle, Teradata and SAP BW) will be available as a downloadable feature pack for Enterprise Edition.

In this Paper I will Explain you how to use Teradata connectivity in SSIS . I am going to explain how to connect ADO NET Source to connect teradata database. How to use parameterized teradata queries in SSIS

Integration services – Teradata  connectivity


For any Dot net application to connect Terdata database we need to have Teradata .Net Provider . This is required for SSIS also .

The Offical downloads for  Teradata .Net data Provider are available at
http://downloads.teradata.com/download/connectivity/dot-net-data-provider

The first step we need to do to connect teradata from SSIS is install  teradata .Net data provider  in the system .



Step 1 : Create connection manager

 

 First create a connection manager with the provider .Net Providers\.Net Data Provider for teradata. Provide the Server name , Username and password which looks

 

 

 

 

 

Step 2 : Create ADO Net Source for connecting teradata database


Drag and drop ADO NET Source into Dataflow task and double click it .
In Connection Manager Section please provide the ADO.NET Connection manager name which was created in my previous step .
There are 2 Data access mode 1. Table or View or 2. Sql command .

If you select sql command , just provide teradata query in sql command text  textbox.
After that click on columns tab which is mandatory .

You can select any destination what you want based on your requirement to load .


 

Step 3 : How to create dynamic or parameterized teradata queries  using ssis ADO NET Source .



This is little bit tricky part we need to implement to have parameterized teradata queries . There is no direct option of using parameters Like OLEDB Source .
We need to fallow these steps .

Step 3.1 : Use Execute SQL task to create parameterized taradata query.


 Use Execute Sql Task to create parameterized query  and final result set should be the Teradata query with embedded parameter values  and assign this result to variable .

For preparing this sql statement you can connect to sql server .

Example :



Sql statement :


select 'SEL ''Commission'' as institution,''ISDN PRA'' as Lines,call_date AS Period_begin,_time_HM AS Period_begin_HM,call_time_sec AS Period_begin_sec,calling_party_cust_id Originating_Institution,nm,tds_pa_id,''BELGIUM'' AS Originating_Country,''K22971112 ''AS fixed_headnum,na_headnum,calling_na Originating_Number,CASE WHEN TRIM(a.traffic_type)||TRIM(called_area_Cd)=''FP0800'' THEN ''FP0800'' ELSE a.traffic_type END AS Destination_type,traffic_type_descr AS Destination_type_descr,code_country,rate_class_desc_en AS Destination_detail,called_ctry_cd AS Destination_ctry_cd,TRIM(called_area_cd)AS Destination_number_area_cd,TRIM(called_access_no)AS Destination_number_access_no,SUM(calls)AS Number_of_Calls,SUM(duration_sec)AS Duration_in_seconds,SUM(duration_sec(DECIMAL(13,2)))/60 AS Duration_in_minutes,SUM(rerated_amount)AS Net_rate FROM
(SEL call_date,call_time_hm,call_time_sec,call_time_hm/100 AS _hour,CASE WHEN call_time_hm - (call_time_hm/100)*100 < 10 THEN ''0''||TRIM(call_time_hm - (call_time_hm/100)*100)ELSE TRIM(call_time_hm - (call_time_hm/100)*100)END         AS _minute,CASE WHEN call_time_hm/100 < 10 THEN ''0''||TRIM(call_time_hm/100)||'':''||TRIM(_minute) ELSE TRIM(call_time_hm/100)||'':''||TRIM(_minute)END AS _time_HM,calling_party_cust_id,calling_party_acct_id,nm,tds_pa_id,TRIM(calling_area_cd)||TRIM(calling_access_no) AS calling_na,product_nr,CASE WHEN rate_class_desc_en LIKE ''%Marian%'' THEN ''MNP''WHEN rate_class_desc_en LIKE ''%micronesi%'' THEN ''FSM''ELSE iso_cd END AS code_country,rate_class_desc_en,traffic_type,called_ctry_cd,called_area_cd,called_access_no,COUNT(*)AS calls,SUM(call_duration) AS duration_sec,SUM(rerated_amount) AS rerated_amount
FROM ( SEL calling_party_acct_id,calling_party_cust_id,product_nr ,traffic_type,call_date,call_time_hm,call_time_sec,calling_area_cd,calling_access_no,called_ctry_cd ,called_area_cd,called_access_no, call_duration,rated_amount,rerated_amount FROM dwhodsv.cob_b_call_mktg_load1 where call_date >= cast ('''+?+'''  as date format''dd/mm/YYYY'')     and   call_date <= cast ('''+?+'''  as date format''dd/mm/YYYY'') 
and calling_party_cust_id in (610959239,610959559 ) AND product_pi IN (''i'',''p'',''m'')
UNION ALL
SEL calling_party_acct_id,calling_party_cust_id,product_nr ,traffic_type,call_date,call_time_hm,call_time_sec,calling_area_cd,calling_access_no,called_ctry_cd ,called_area_cd,called_access_no, call_duration,rated_amount,rerated_amount  FROM dwhodsv.cob_b_call_mktg  where call_date >= cast ('''+?+'''  as date format''dd/mm/YYYY'')     and   call_date <= cast ('''+?+'''  as date format''dd/mm/YYYY'')
and calling_party_cust_id in (610959239,610959559 ) AND product_pi IN (''i'',''p'',''m'')
)a
LEFT JOIN dwhcdbv.cdb_b_account_active c
ON a.calling_party_acct_id = c.party_acct_id
AND a.calling_party_cust_id = c.party_cust_id
AND c.party_cust_id  IN (610959239,610959559 )
LEFT JOIN P0_BUS_EUDSv.DSC_UACC_S_RATE_CLASS g
ON a.product_nr = g.base_product_nr
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 ) a
LEFT JOIN dwhrefv.traffic_type b
ON a.traffic_type = b.traffic_type
AND lang = ''N''
LEFT JOIN (sel a.party_cust_id,b.na_headnum,a.na FROM dwhcdbv.cdb_b_subs_na_line a
left join dwhabdv.abd_na b on a.na=b.na
WHERE  
(a.na between  ''022920000''AND ''022929999'' OR a.na BETWEEN ''022950000'' AND ''022959999'' OR a.na BETWEEN ''022960000'' AND ''022969999'' OR a.na BETWEEN ''022970000'' AND ''022979999'' OR a.na BETWEEN ''022980000'' AND ''022989999''OR a.na BETWEEN ''022990000''AND ''022989999'' OR a.na BETWEEN ''022823700'' AND ''022823709'' OR a.na BETWEEN ''027393090'' AND ''027393099'')
) c
ON a.calling_na=c.na
WHERE 
(TRIM(calling_na) BETWEEN ''022920000''AND ''022929999''OR TRIM(calling_na) BETWEEN ''022950000''AND ''022959999'' OR TRIM(calling_na) BETWEEN ''022960000'' AND ''022969999'' OR TRIM(calling_na) BETWEEN ''022970000''AND ''022979999'' OR TRIM(calling_na) BETWEEN ''022980000''AND ''022989999'' OR TRIM(calling_na) BETWEEN ''022990000'' AND ''022999999'')
 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19'


delete ODS_Schema.VA_LOT1_Details
where  MONTH(period_begin) = month(DATEADD(dd,-(DAY(GETDATE())),GETDATE()))
and Institution ='Commission' and Lines ='ISDN PRA'

Parameter mapping looks like this .

 

 

The final result should be the teradata query which returned to variable  like

 


 Step 3.2 : How to create dynamic or parameterized teradata queries  using ssis ADO NET Source .


After you created dataflow task as mentioned in Step 2  which include ADO NET source and any destination in dataflow task window  come back to control flow level and right click DataFloaw task to go properties .

In Expressions Property
Select   [ADO NET Source].[Sql command]  property and value of variable where we are storing final teradata query in our previous step  ie  @[User::EC_ISDNPRA]


 

Limitation :  The Final Query shouldn’t cross 4000 characters . it is a limitation of the expression feature in SSIS.

Comments

Post a Comment

Popular posts from this blog

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System".
Read more

SSIS Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces

Image
Problem : When we have Multiple Namespaces in the Xml Source which we need to load the Data from   as we shown below Then we will face a issue in generating XSD for that XML Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio) Solution : First we need to avoid multiple namespaces to transform your source file to a format that doesn’t refer to the namespaces. SSIS has an XML task that can do the transformation. 1) Add the XML task to an SSIS   Control Flow and edit it. 2) Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem. 3) Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file ( ModifiedInput.XML). 4) Add the below script to a new file and save it with an xslt file extension (Input.XSLT) <
Read more