Pass dynamic SQL query to ADO.net connection

gravatar

Peter Avenant

Answer to question from LinkedIn User Group. Trying to generate a dynamic statement to pass to an ADO.net 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


Share

                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
<# 	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">
			<Connections>
				<Connection ConnectionName="BIMP" />
				<Connection ConnectionName="MYSQL_SRC" />
			</Connections>
			<Variables>
				<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>
		    </Variables>
			<Tasks>
				<ExecuteSQL Name="SQL - Get Last Load Id" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
					<Results>
						<Result Name="0" VariableName="User.LastLoadId" />
					</Results>
					<DirectInput>SELECT	[VariableValue]
		FROM	[ssis].[ConfigVariable]
		WHERE	[VariableName] = 'LastLoadId'</DirectInput>
				</ExecuteSQL>
				<ExecuteSQL Name="SQL - Get Last Load Date" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
					<Results>
						<Result Name="0" VariableName="User.LastLoadDate" />
					</Results>
					<DirectInput>SELECT	[VariableValue]
		FROM	[ssis].[ConfigVariable]
		WHERE	[VariableName] = 'LastLoadDate'</DirectInput>
				</ExecuteSQL>
				<Dataflow Name="Load Table <#=tableName.Replace(".", " ") #>">
					<Expressions>
						<Expression ExternalProperty="[ADO_SRC <#=tableName.Replace(".", " ") #>].[SqlCommand]">@[User::extractSql]</Expression>
					</Expressions>
					<Transformations>
						<AdoNetSource Name="ADO_SRC <#=tableName.Replace(".", " ") #> " ConnectionName="MYSQL_SRC" ValidateExternalMetadata="false">
							<DirectInput>
								<#=sourceQuery #>
							</DirectInput>
						</AdoNetSource>
					</Transformations>
				</Dataflow>
			</Tasks>
		</Package>
	</Packages>
</Biml>
                        
You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.