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