July 1, 2004 at 11:52 pm
Is there a way to script out a dts package. I would like to be able to place @variables within it and run it to make it dynamic (as oppossed to using the dynamic task). I have not seen anyone mention this, so I think it cannot be done.
Thanks
smv929
July 2, 2004 at 10:05 am
Package
Save As
Visual Basic File
But why don't you want to use the Dynamic Properties Task? It's easy and extremely flexible.
Did you know you can also pass Global Vars in and out of tasks throughout your package? Create an Output Var from either an ExecSQL task or an ActiveX task. Say for example, you want to capture the max report date from one table and use it in a transformation later in the package. Your first ExecSQL statement will be "select max(reportdate) from mytable". Click on parameters and make it an output parameter, assigning it to a gdMaxReportDate. Then in your transformation, put a ? like this: "select field1, field2 where reportdate = ?" Click on parameters and assign gdMaxReportDate as the input variable to parameter1. Isn't that cool? By the way, parameter1 is the first ?. If you have a second ?, that will be parameter2, and so on - based on the order in which they appear. You can also use these ?'s in DTS Lookups. Also pretty cool.
Parameterized queries and lookups can be slow, though. So you should also consider using variables in your T-SQL scripts when appropriate. For example:
--you can put all of this in a transformation "query"
--get your variable
declare @dReportDate as datetime
set @dReportDate = (select max(ReportDate) from tbl_myTable)
--this select statement then becomes the basis of your transformation
select field1, field2 from tbl_myOtherTable where ReportDate = @dReportDate
[font="Courier New"]ZenDada[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply