• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

sql srvr 2k DTS Pkg Parameter How To: Use the result from a exec sql task as a parameter in a data trans. sql query

I have a DTS Pkg with the following objects:
Exec Sql:
select Min(TheDate) as TheDate from MyDateTable

Upon success, I want it to run the following:
Source --Srvr1.DB1
(linked server connection)
select MyInfo1 from MyInfoTable
where MyInfoDate = TheDate --->>From the first Exec Sql

Destination Srvr2.DB2

Transform
Myinfo1 --> MyInfo2

Is it possible to assign the result of the first Exec Sql to a global variable and use it in the Data Transform?
Sami

0
tf842
Asked:
tf842
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
>>Is it possible to assign the result of the first Exec Sql to a global variable and use it in the Data Transform?<<
Yes.  Click on the "Parameters ..." button and then select the "Output Parameters" tab.
0
 
tf842Author Commented:
I tried that to no avail. I guess the real question may be how to set that up as a parameter.
0
 
Anthony PerkinsCommented:
Ok.  Let's go step by step:

1. In your Execute SQL Task Properties dialog with "select Min(TheDate) as TheDate from MyDateTable", click on "Parameters ..."
2. Select the Output Parameters tab
3. In Output Parameter Type select "Row Value"
4. For the Parameter "TheDate" select from the drop down the appropriate Global Variable.
5. Select OK
6. Select OK


0
 
tf842Author Commented:
ac,
I did as you suggested above.

To clarify for those reading this solution, I needed to put a ? in place of the parameter name in the DT Sql Query:
Source --Srvr1.DB1
(linked server connection)
select MyInfo1 from MyInfoTable
where MyInfoDate = ? --->>From the first Exec Sql

The 1st ? uses the top parameter in the global parameter list.
The 2nd ? would use the 2nd, etc.

ac, Please reply noting any corrections to this and referencing my comment in your final comment. Then I will assign the points with an A. Your promptness was great.
Sami
0
 
Anthony PerkinsCommented:
>>I needed to put a ? in place of the parameter name in the DT Sql Query<<
Yep, you are right I overlooked that. And yes, the order of the parameters is important.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now