I read several posts on this topic and I would like to confirm my understanding. This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement. But in researching further, it looks like I need to store theentire SQL command in a one long string variable (let's call it A), where A is built off of another variable that hasmy parameter.I thenuse A in Data access mode: SQL command from variable. Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value? It seems a bit clumsy to store the entire SQL expression in a string variable.

If you use the SQL Command option, you can use parameter placeholders (?) in the query, and map variables to them using the Parameters button. Code SnippetSELECT * FROM Table1 WHERE colA = ?

jwelch wrote: If you use the SQL Command option, you can use parameter placeholders (?) in the query, and map variables to them using the Parameters button. Code SnippetSELECT * FROM Table1 WHERE colA = ? Oh, that's good. So why are people using the "SQL command from variable" option which requires two variables? (Assuming my understaning of SQL command from variable" is correct.)

MostOLE DB providers don't handle complex parameterization very well. For example, the example above should parse fine. A more complex one, like: Code Snippet SELECT * FROM ( SELECT * FROM TableX WHERE ID = (SELECT TOP 1 ID FROM TableY WHERE UpdateDate BETWEEN ? AND ?)) will probably fail to parse. In that case, your only option is to build the query in a variable. Personally, I use the SQL Command from variable option pretty much all the time. I have a variable that's based on an expression that concatenates the SQL statement with other variables to assemble the final statement. It's only one additional variable, and it gives you more flexibility. Still, there is a 4000 character limit on the query if you build it through expressions, but I rarely hit that.

Got it, thx.

Hi,I'm trying to use the "SQL Command from Variable" with parameters and my question is: Where do I put my parameters ? I mean, a wrote my SQL Command but how do I pass my parameters ?Thanks,Levogiro

You don't "pass parameters" when you're doing the SQL Command from Variable. You build up your SQL command with the parameters "in it" already.Create an SSIS variable, and mark it as "Evaluateas Expression". Now edit the expression so that instead of saying something like this (a parameterized query): SELECT* FROM( SELECT*FROMTableXWHEREID=(SELECTTOP1IDFROMTableYWHEREUpdateDateBETWEEN?AND?)) The expression is written something like this: "SELECT*FROM(SELECT*FROMTableXWHEREID=(SELECTTOP1IDFROMTableYWHEREUpdateDateBETWEEN'" +(DT_WSTR,20)@User::FromDate&"'AND'"+(DT_WSTR,20)@User::ToDate+"'))" The resulting "value" of the variable will be a non-parameterized query: SELECT*FROM(SELECT*FROMTableXWHEREID=(SELECTTOP1IDFROMTableYWHEREUpdateDateBETWEEN'2000-01-01'AND'2008-01-01'))" (However, I'm sure I'm oversimplifying the conversion between an SSIS date typed variable to DT_WSTR - I don't think that's what SSIS will come up with on its own - I've never tried, I've always explicitly casted the year, month and day separately using DatePart...)

Hello Mr.Todd, I want to create a multiple .csv files from only one OLE DB source, I took three variables 1.strTblName(For getting the table names from database) 2.strQuery (In Eval as Expression Property as "Select top 10 * from "+ @[User::strTblName]) 3.Object and i took ForEach Loop Container and enumerator as SMO, selected the table option in Specific DB, Enumerate type as "Names" In variable Mapping, took the variable strTblName, In ForEach, dragged the ExecuteSqlTask, for SqlSourceType as Variable and took strQuery In dataflow task,dragged the OLE DB Source, in that DataAccessMode as "Sql Command From Variable" But, I'm Getting Error Like this ....See Below:: Error::[SSIS.Pipeline] Error: "component "OLE DB Source" (135)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". Warning:[OLE DB Source [135]] Warning: The external columns for component "OLE DB Source" (135) are out of synchronization with the data source columns. The column "SystemInformationID" needs to be added to the external columns. The column "Database Version" needs to be added to the external columns. The column "VersionDate" needs to be added to the external columns. The external column "rowguid" (189) needs to be removed from the external columns. The external column "AdditionalContactInfo" (186) needs to be removed from the external columns. The external column "PasswordSalt" (183) needs to be removed from the external columns. The external column "PasswordHash" (180) needs to be removed from the external columns. The external column "Phone" (177) needs to be removed from the external columns. The external column "EmailPromotion" (174) needs to be removed from the external columns. The external column "EmailAddress" (171) needs to be removed from the external columns. The external column "Suffix" (168) needs to be removed from the external columns. The external column "LastName" (165) needs to be removed from the external columns. The external column "MiddleName" (162) needs to be removed from the external columns. The external column "FirstName" (159) needs to be removed from the external columns. The external column "Title" (156) needs to be removed from the external columns. The external column "NameStyle" (153) needs to be removed from the external columns. The external column "ContactID" (150) needs to be removed from the external columns. ... I knew that the SSIS package is case sensitive, but i want to get all the .csv files .. Thanking You.

I think from the Execute SqlTask only the Columns are not matching What to do ...Is there any solution for it ....

@Vijay:Duplicate post, already posted here :http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a737184e-c7ce-4990-b475-61cadaffa96c Thanks