Pass dynamic SQL query to connection


Peter Avenant

Answer to question from LinkedIn User Group. Trying to generate a dynamic statement to pass to an connection (MySQL), but Ado does not have SQL command from variable. Will overriding the SQL command property with an expression at the connection level work?

published 01.11.15

last updated 01.11.15



<Biml xmlns="">
<# 	var tableName = "my.table";
	var expressionSql = "SELECT * FROM " + tableName +" WHERE modified_date &gt; '\" + @[User::LastLoadDate] + \"' AND table_id &lt;= \" + @[User::LastLoadId] + \";";
	var sourceQuery = "SELECT * FROM " + tableName + ";";#>
		<Package Name="Extract <#=tableName.Replace(".", " ") #>" ConstraintMode="Linear">
				<Connection ConnectionName="BIMP" />
				<Connection ConnectionName="MYSQL_SRC" />
				<Variable Name="extractSql" DataType="String" EvaluateAsExpression="true"><#=expressionSql#> </Variable>
				<Variable Name="LastLoadId" DataType="String" IncludeInDebugDump="Exclude">0</Variable>
                <Variable Name="LastLoadDate" DataType="String" IncludeInDebugDump="Exclude">1900-01-01 00:00:00</Variable>
				<ExecuteSQL Name="SQL - Get Last Load Id" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
						<Result Name="0" VariableName="User.LastLoadId" />
					<DirectInput>SELECT	[VariableValue]
		FROM	[ssis].[ConfigVariable]
		WHERE	[VariableName] = 'LastLoadId'</DirectInput>
				<ExecuteSQL Name="SQL - Get Last Load Date" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
						<Result Name="0" VariableName="User.LastLoadDate" />
					<DirectInput>SELECT	[VariableValue]
		FROM	[ssis].[ConfigVariable]
		WHERE	[VariableName] = 'LastLoadDate'</DirectInput>
				<Dataflow Name="Load Table <#=tableName.Replace(".", " ") #>">
						<Expression ExternalProperty="[ADO_SRC <#=tableName.Replace(".", " ") #>].[SqlCommand]">@[User::extractSql]</Expression>
						<AdoNetSource Name="ADO_SRC <#=tableName.Replace(".", " ") #> " ConnectionName="MYSQL_SRC" ValidateExternalMetadata="false">
								<#=sourceQuery #>
You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.




3:23pm 05.03.18

When selecting the source after generation the Data access mode is set to 'Table or View'.

The meta data is based on the query in the direct input and not the Expression (which I guess is why you use the same query in the 'source query' variable.

After generation I have to manually set the access mode to SQL command to see the correct query. Is it possible to set this property within the framework?