Skip to Content
0
Former Member
Jan 11, 2011 at 01:40 PM

Passing Time Parameter to a SQL Script

27 Views

I am trying to create an SSIS package that will extract data from a non BPC database to load into BPC. I am calling the package from DM prompting for the time parameter. However, I am having problems passing the parameter to the SQL script. I am using OLEDB Data source task using the SQL command - Using the following scrip where the '?'' is the prompt for the BPC time parameter. Any suggestions on how to resolve would be greatly appreciated.

SELECT

CASE

WHEN LEN(GLAccountNumber)=10

THEN LEFT(GLAccountNumber,6)

ELSE GLAccountNumber

END AS ACCOUNT ,

'F_CLO' AS ACCTDETAIL ,

'ACTUAL' AS CATEGORY ,

RTRIM(CompanyCode) AS COMPANY ,

RTRIM(CompanyDepartment) AS COSTCENTER ,

'FM' AS DATASRC ,

'NON_INTERCO' AS INTERCO ,

'NOMAT' AS MATERIALS ,

'NOPC' AS PROFITCTR ,

'LC' AS RPTCURRENCY ,

CAST(YearNumber AS CHAR(4)) + '.' + MonthAbbreviation AS [TIME] ,

SUM(TransactionAmount) AS SIGNEDDAA

FROM dbo.GLTransactionDimension a WITH (nolock)

JOIN dbo.GLTransactionFacts b WITH (nolock)

ON a.GLTransactionDimensionKey = b.GLTransactionDimensionKey

JOIN dbo.DateDimension c WITH (nolock)

ON b.EntryDateDimensionKey = c.DateDimensionKey

WHERE EntryDateDimensionKey LIKE

CASE

WHEN RIGHT(?,3)='APR' THEN '200904%'

WHEN RIGHT(?,3)='MAY' THEN '200905%'

END

AND RowType = 'T'

GROUP BY CompanyCode ,

DepartmentCode ,

CompanyDepartment ,

GLAccountNumber ,

YearNumber ,

MonthAbbreviation

ORDER BY CompanyCode ,

DepartmentCode ,

GLAccountNumber ,

YearNumber ,

MonthAbbreviation