Data Transformation Task: Can I pass a variable?

Question

Post reply

Data Transformation Task: Can I pass a variable?

LMSJXS

Old Hand

Points: 358
More actions
June 3, 2004 at 10:44 am

#59835

Currently I have a DTS package that has a Transfrom Data task that pulls rows from a Mainframe, based on a date value greater than X, and another field has a specific value.
Here is the query:
select dnrt_phone_numb,dnrt_agency,dnrt_in_date
from super_dba.dncrt
WHERE DNRT_IS_DATE > 20040601 OR
        DNRT_FG_DATE > 20040601 OR
        DNRT_DM_DATE > 20040601 OR
        DNRT_ST_DATE > 20040601 AND
        DNRT_AGENCY NOT IN ("C")
The from table is on the mainframe, the destination table is setup under the Destination tab of the Transform, and is a SQL Server table (DNCRT.DBO.DNCXFER).
I have to update the date value daily(DNRT_*_DATE), and would like to be able to do it programatically to make it automated.
Is it possible to pass a variable to the Transform Data Task? What I am looking for is to pass a system date(?) to the task so that I only pull rows that have a date greater than the current date. Is this even possible?
I have tried to put the date into a SQL Server table, and pull it from there, but the query fails, saying that the table doesn't exist(my thinking is that is looking for it on the Mainframe, not on SQL Server).
Dan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

Search for "Lookup Queries" in BOL. It has exactly what you are looking for.

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

I just noticed you are possibly going to have trouble with your query. It should probably look like this:

select dnrt_phone_numb,dnrt_agency,dnrt_in_date

from super_dba.dncrt

WHERE (DNRT_IS_DATE > 20040601 OR

DNRT_FG_DATE > 20040601 OR

DNRT_DM_DATE > 20040601 OR

DNRT_ST_DATE > 20040601) AND

DNRT_AGENCY NOT IN ("C")

Otherwise, the DNRT_AGENCY criteria will only apply to DNRT_ST_DATE. If your original query is really what you want, please disregard my butting in.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

LMSJXS Old Hand Points: 358 More actions · Answer 3

Nice catch, I didn't create the query, but I will get with the Analyst that did to verify with him exactly what he is pulling. I just got pulled in since they want to automate it.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 4

Hi,

In order to pass the value to the query dynamically, you have to go for Global variable. Then your query would look like this

select dnrt_phone_numb,dnrt_agency,dnrt_in_date

from super_dba.dncrt

WHERE (DNRT_IS_DATE > ? OR

DNRT_FG_DATE > ? R

DNRT_DM_DATE > ?OR

DNRT_ST_DATE > ?) AND

DNRT_AGENCY NOT IN ("?")

in the Transform Data Task, Source tab, select the SQL Query radio button and click the parameters. Before that you add the global variable and assign to whatever order.

If you are running the package from an application set the global variable from your application and make sure the dynamic properties is the first task before you run your actual transformation which initialize the global variable.

If you require further help, I can send the screen shot of each and every step also.

Thanks,

Ganesh

LMSJXS Old Hand Points: 358 More actions · Answer 5

Ganesh,

I can set up the Global Variable, however I get an error under the Transform Data task when selecting the Parameters option:

Package Error:

Error Source: Microsoft OLE DB Provide for ODBC Drivers

Error Description: Provide cannot derive parameter information and SetParameterInfo has not been called.

Does ODBC not support the passing of Parameters(The source is an ODBC Connection to the Tandem Mainframe)? Or is it the driver that is being used?

I can setup the parameter when the table is a local DB in Sql Server.

-Dan

ipTomN Say Hey Kid Points: 690 More actions · Answer 6

If you cannot setup parameter - you can use ActiveXScript in the worklflow tab

in which you will dynamically change your query without passing parameter.

I'll give you an example where I assigned the whole SQL Statement ( where I could not use parameter):

You can easily modify the script to suit your needs.

If you need any help with that just let me know - I do not have too much time at the moment.

It's start work here down under.

'-------------------------------

Function Main()

Dim strBody

Dim objPackage

Dim strSqlStatementNew

Dim objSQLStatement

Dim taskprops

' STOP

Set objPackage = DTSGlobalVariables.Parent

strPkgName = objPackage.Name

strPkgDesc = objPackage.Description

strLogFileName = objPackage.LogFileName

strSqlStatement = ""

For Each objTask in objPackage.Tasks

' ` if task is ExecutePackageTask type

If LEFT(objTask.Name, 26) = "DTSTask_DTSExecuteSQLTask_" THEN

IF UCASE(Left(objTask.Description, 19)) = UCASE("SetRestrictedAccess") THEN

' sets variables for properties collection and SQLStatement property object

Set taskprops = objTask.Properties

SET objSQLStatement = taskprops("SQLStatement")

strSQLStatementNew = "ALTER DATABASE " & DTSGlobalVariables("gvDBNamePrefix").Value & vbCRLF & _

"SET RESTRICTED_USER"

' assigns back value to the task SQLStatement property

objSQLStatement.Value = strSQLStatementNew

' EXIT FOR - there are more than one - just for simplicity change them all

END IF

End If

End Function

'-------------------------------------------------------------

Thanks

Tom

jfb SSCrazy Points: 2469 More actions · Answer 7

here is a nice example in how to use global variables and sp's

http://www.sqldts.com/default.aspx?234

JFB