DTS Package Parameter Passing

Question

DTS Package Parameter Passing

Diorelle Ramos

SSC Rookie

Points: 34
More actions
August 18, 2006 at 2:36 am

#76568

I've created a Stored Procedure called GetFlashExportValues. See Below:
CREATE PROCEDURE [dbo].[GetFlashExportValues]
@WhereClause varchar(1000) = NULL
AS
DECLARE @sql varchar(5000)
SET @sql=
'SELECT DISTINCT
mr.GroupCode , md.PC, mi.RootControlName, md.YearNumber, fc.FISCAL_PERIOD_NUMBER , mm.ServiceType, md.StringMetricValue, md.NumericMetricValue

FROM
MetricIdents mi INNER JOIN MetricData md ON mi.RowKey=md.MetricIdentKey
   INNER JOIN MetricMappings mm ON mi.RowKey=mm.MetricIdentKey
   INNER JOIN MetricReportTypes mr ON mr.ReportTypeID=mm.ReportTypeID
   INNER JOIN (SELECT DISTINCT FISCAL_PERIOD_MONTH, FISCAL_PERIOD_NUMBER from fiscalcalendar
WHERE SUBSTRING(FISCAL_PERIOD_NAME,1,3) = SUBSTRING(FISCAL_PERIOD_MONTH,1,3)) fc
   ON md.MonthText=fc.FISCAL_PERIOD_MONTH
WHERE
mm.ExportToWareHouse=1 AND mm.RollupExport=0'
IF @WhereClause != NULL
BEGIN
SET @sql = @sql + ' AND ' + @WhereClause
END
EXEC (@sql)
GO
I then created a package to call this stored procedure and wanted to pass a value for the parameter @WhereClause from the command line. I was thinking of using the /A of dtsrun..
e.g. 'DTSRun /S "(local)" /U "user" /P "pass" /N "FlashExportValues" /A "@WhereClause":"8"="""MonthText=''August'' AND YearNumber=2006"""'
but I wasn't sure how to map it to the @WhereClause I declared in the code of the stored procedure.. can somebody show me how please? I'd really appreciate it. Thanks a bunch!

Viewing 3 posts - 1 through 3 (of 3 total)

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

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

Make sure you have a global variable in the DTS package named "@WhereClause".

Open the Execute SQL task where you're calling the stored procedure and make sure the SQL Statement looks similar to this:

EXEC dbo.GetFlashExportValues ?

The ? is important. Click on the Parameters button and go to the Input Parameters tab of the window that appears. Select your "@WhereClause" global variable from the drop down list. The global variable is now mapped to the parameter of your stored procedure.

Diorelle Ramos SSC Rookie Points: 34 More actions · Answer 2

so you mean to say for as long as I have the same global variable name as the variable i have in the stored procedure code, it would be mapped? thanks by the way!