SSIS: How to pass variables to ADO.NET Source

Hey,  Trouble in mapping parameter or vaiable? This should be fairly a simple thing to do, why the SSIS Designer is showing so many issues ?. If you are one among those asking that question, hope this post will help you.

This post is about mapping a variable in any SSIS data elements, i.e ADO.NET Data Source, SQL tasks ,OLE DB source etc.

To use expressions for an ADO.NET source in a data flow, here are a few simple steps:

Step 1 – Go to the Data Flow Task Properties window

On the Control Flow tab (not the Data Flow tab), right-click the Data Flow Task you are working on and select Properties.

ssis-ctrlflow-menu

Step 2 – Open the Property Expression Editor

In the Properties window, find a property called Expression and a small button next to it. Click the button to open the Expression Editor.

ssis-ctrlflow-menu3


Step 3 – Open the Expression Builder for SQL Command

In the Property list, select [ADO NET Source].[SqlComamnd] and click the button next to the Expression column to open Expression Builder.

Step 4 – Write query with variables

Start to write your query with variable names and click the Evaluate Expression button to test the expression.

Example : “Select col1,col2,col3 from Table Where id=” +  @[User::ParamId]

ExpressionBuilder

You may get an error “the data types dt_wstr and dt_i4 are incompatible for binary operator +” , if an incompatibledata type used for concatenation.

Either cast the variable a string or convert variable type to String for concatenation operation.

“Select col1,col2,col3 from Table Where id=” + (DT_WSTR,10) @[User::ParamId]

By default variable will be created as Boolean, to convert check variable type expand the variable explorer window. Visual Studio. Menu SSIS >> Variables

ssis-var-menu

Note: You can change the DelayValidation property to true, so you do not need to provide an initial value to the variable holding the parameter value, otherwise the package could fail validation.

Reference:

How to: Map Query Parameters to Variables in an Execute SQL Task

http://msdn.microsoft.com/en-us/library/ms140355(v=sql.105).aspx

Difference when using an expression Vs Parameters:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2415979&SiteID=1

Thanks for reading. Hope this help

Advertisements
This entry was posted in SSIS and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s