Hello , Can some one advise whats wrong with this inline query which uses CTE : This code gives me error when hooked in OLEDB Source and with SQL Command data acess mode saying "syntax error,permission violation or other non specific error" WITH CTE AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY MRN) AS ID FROM [BULK] ) SELECT * FROM CTE WHERE ID BETWEEN (?) AND (9999+?) However this code previews fine : WITH CTE AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY MRN) AS ID FROM [BULK] ) SELECT * FROM CTE Thanks in advance ! Rajkumar Yelugu

Hi, you want to pass two parameters for the query right? I think that this is the issue here.. Try adding a variable to build the query (using SSIS expressions) and then use SQL Command from variable in your OLE DB Source. David.

Hi, you want to pass two parameters for the query right? I think that this is the issue here.. Try adding a variable to build the query (using SSIS expressions) and then use SQL Command from variable in your OLE DB Source. David. David , Thanks for your response . I have variable as desired .But where the issue crepts up is when i hook that code there and click on the parameters tab . You want me to build a query tie it up to a vairable and use it ? Thanks ! Rajkumar Yelugu

in ssis parameters with cte is not appropriate, in source you have to write simple select and use a parameter in where only.. Regards, nishant Please mark posts as answered where appropriatenishantcomp2512

You can try this: DECLARE @start INT, @end INT; SET @start = ?; SET @end = ?; WITH CTE AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY MRN) AS ID FROM [BULK] ) SELECT * FROM CTE WHERE ID BETWEEN (@start) AND (9999+@end); I would advice against using SELECT * in your source, as it can add columns to your input if the table changes and it will break your package. MCTS, MCITP - Please mark posts as answered where appropriate.