October 20, 2010 at 8:06 am
I have a DTS Package basically pulls data from 2 tables on Teradata. But there is a ActiveX task used in DTS to make connecitons for pulling it.
There is a work unit that executes a DTS package that is pulling data from the v_acct_master_yyyymm_hd and v_acct_cis_unsec_yyyymm_hd views on Teradata.
I want to get it converted to SSIS. I have used .NET provider for Teradata Connection manager in SSIS coz that is what they have previously used for pulling data from teradata and basically using OLEDB destination for putting into a SQL table. I am very new to the SSIS script task and basically confused bcoz I think the connections used are varying based on table names with
yyyymm appended in ending.
Could anyone please guide me how to achive the same functionality in SSIS
I have attached an image of the DTS package.
I am putting a VB6 code that is used in Activex task(Set Date task) in DTS for better understanding.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPackage
Dim oADO
Dim sPassword
Dim sLoginID
Dim sServerName
Dim rs
Dim sSql
Dim sSqlView1
Dim sCurrDate
Dim sOpenDate
Dim sBirthDateStart
Dim sBirthDateEnd
Dim dCurrentDate
Dim dOpenDate
Dim dBirthDateStart
Dim dBirthDateEnd
' Creating Package and ADO objects.
set oPackage = DTSGlobalVariables.Parent
set oADO = CreateObject("FWBADO.clsGlobal")
' Dynamically setting connection object for dbMarketing
sPassword = oADO.ADOConnection("dbMarketing").Password
sLoginID = oADO.ADOConnection("dbMarketing").LoginID
sServerName = oADO.ADOConnection("dbMarketing").ServerName
oPackage.Connections("dbMarketing").Properties("UserID").Value = sLoginID
oPackage.Connections("dbMarketing").Properties("Password").Value = sPassword
oPackage.Connections("dbMarketing").Properties("DataSource").Value = sServerName
oPackage.Connections("dbMarketing").Properties("UseTrustedConnection").Value = 0
' Dynamically setting connection object for dbPacksTera
sSql = "uspDBConnectionSel 'dbPacksTera'"
Set rs = oADO.ADOConnection("dbSupport").OpenRecordset(sSql)
If Not rs.EOF Then
oPackage.Connections("Teradata").Properties("UserID").Value = rs("LoginID")
oPackage.Connections("Teradata").Properties("Password").Value = rs("Password")
oPackage.Connections("Teradata").Properties("DataSource").Value = rs("Server")
Else
Err.Raise 50002, "pkgStudentDataLoad", "Connection information was not found for database. Database was either misspelled or not found in the tblConnection table."
End If
rs.Close
Set rs = Nothing
dCurrentDate = DateAdd("d", -1, DatePart("m", Date()) & "/1/" & DatePart("yyyy", Date()))
dOpenDate = DateAdd("m", -60, dCurrentDate)
dBirthDateStart = DateAdd("d", 1, DateAdd("yyyy", -26, Date()))
dBirthDateEnd = DateAdd("yyyy", -18, Date())
sCurrDate = DatePart("yyyy", dCurrentDate) & Right("00" & DatePart("m", dCurrentDate), 2)
sOpenDate = DatePart("yyyy", dOpenDate) & "-" & Right("00" & DatePart("m", dOpenDate), 2) & "-" & Right("00" & DatePart("d", dOpenDate), 2)
sBirthDateStart = DatePart("yyyy", dBirthDateStart) & "-" & Right("00" & DatePart("m", dBirthDateStart), 2) & "-" & Right("00" & DatePart("d", dBirthDateStart), 2)
sBirthDateEnd = DatePart("yyyy", dBirthDateEnd) & "-" & Right("00" & DatePart("m", dBirthDateEnd), 2) & "-" & Right("00" & DatePart("d", dBirthDateEnd), 2)
sSqlView1 = "SELECT " & vbcrlf
sSqlView1 = sSqlView1 & " A.acct_num AS AcctNum_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.acct_status AS AcctStatus_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.au_num AS AUNum_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.co_id AS COID_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.open_dt AS OpenDT_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.pcode AS PCode_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.pkg_cd AS PkgCD_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.primary_tin AS PrimaryTin_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " A.ptype AS PType_MA, " & vbcrlf
sSqlView1 = sSqlView1 & " B.addr_1 AS Addr1_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.addr_2 AS Addr2_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.addr_3 AS Addr3_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.birth_dt AS BirthDT_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.city AS City_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.home_phone AS HomePhone_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.name_1 AS Name1_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.primary_tin AS PrimaryTin_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.state AS State_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.zip AS Zip_CI, " & vbcrlf
sSqlView1 = sSqlView1 & " B.zip4 AS Zip4_CI " & vbcrlf
sSqlView1 = sSqlView1 & "FROM bmgp.v_acct_master_" & sCurrDate & "_hd A " & vbcrlf
sSqlView1 = sSqlView1 & " INNER JOIN bmgpfcra.v_acct_cis_unsec_" & sCurrDate & "_hd B ON A.acct_num = B.acct_num AND A.co_id = B.co_id " & vbcrlf
sSqlView1 = sSqlView1 & "WHERE A.acct_status IN ('1','5','9','99') " & vbcrlf
sSqlView1 = sSqlView1 & " AND ((A.open_dt > '" & sOpenDate & "') OR (A.open_dt IS NULL)) " & vbcrlf
sSqlView1 = sSqlView1 & " AND ((B.birth_dt BETWEEN '" & sBirthDateStart & "' AND '" & sBirthDateEnd & "') OR (B.birth_dt IS NULL)) " & vbcrlf
sSqlView1 = sSqlView1 & " AND B.state IN('AK', 'AZ', 'CA', 'CO', 'IA', 'ID', 'IL', 'IN', 'MI', 'MN', 'MT', 'ND', 'NE', 'NM', 'NV', 'OH', 'OR', 'SD', 'TX', 'UT', 'WA', 'WI', 'WY') " & vbcrlf
oPackage.Tasks("DTSTask_DTSDataPumpTask_2").Properties("SourceSQLStatement").Value = sSqlView1
Set oPackage = Nothing
Set oADO = Nothing
Main = DTSTaskExecResult_Success
End Function
October 21, 2010 at 9:22 am
It looks like a you could use a Data Flow task to accomplish this. You should store your login info in a Package configuration and set up your connection manager to use it. You would use a Script Task to construct the dynamic SQL statement - pretty much using that same piece of code from your Active X task - and assign that to a Package string variable. Then set the SQL Command property of the Data Reader source = to the package variable.
When setting up the Data Reader component the first time around, you'll want to hard code the sql statement, just so the it will populate the metadata needed by SSIS. Then set the SQL command to be dynamic using the variable.
October 21, 2010 at 11:20 am
Thanks for replying....thats the point I am stuck at. Well I wonder, Let me re-collect what you just said is...like first i have to hard code the SQL (select statement used) in the Component properties ->Sql command ....
And the run the package or should I ->
then set up the Expressions of the dataflow task by setting up [TeraData Source]:[SQL Command] = @[User::Variablename]
Do i just have to use @[User::Variablename] in the expressions - or do i have to write something else
And then Run the package.
Can you please tell whether like after the initial run - Do I have to go and delete that hardcoded sql?
Or just after initial run this will run just dynamically fetching the values from variable?
October 21, 2010 at 11:44 am
You don't necessarily have to execute the package. In the Data Reader Source component, you will note that you have nothing in the Column Mapping tab until it has a SQL statement to work with. If you hard code the initial statement there and then switch to column mapping, it will pick up the metadata that it needs. I just find it easier to get this step done with a hardcoded statement that I know works, rather than relying on expressions and variables and possible issues involved with those.
Once you set the SQL Command property expression to use the variable value, the Data Reader will use that instead of the hard-coded statement. As a matter of fact, if you remove the hard coded statement in the Data Source editor, it will bark at you. If you want to clear it, then use the Properties window.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply