How to pass variable to sql query of ODBC Source in SSIS?

Question

I am using ODBC source in Data flow task of SSIS. I want to select only latest data from ODBC source,

Here is my query:

Select * from ODBCTable where date >= @[user::date1]

But i am getting error while parsing query.

What is the alternative way this? How can i pass the variable and how to create effective variable to pass the query that can give me only latest today's date data.

I have defined variable but how can i use ? insted of @[user::date1], how to map these. — Jack
– Jack, Commented Dec 30, 2015 at 11:35
where you define the variable there is one mapping tab within that map the variable and with your where clause variable and put question mark after equal sign — wiretext
– wiretext, Commented Dec 30, 2015 at 11:41

tember · Accepted Answer · 2016-09-28 17:51:38Z

2

In the SQL Command text, first declare your variable then write your sql statement:

declare @user_date1 date = cast(? as date)

Select * from ODBCTable where date >= @user_date1

Then click on the Parameters button to map an SSIS parameter value to your sql variable. The mapping is done in order of appearance in your SQL Command - not by name.

answered Sep 28, 2016 at 17:51

tember

1,5182 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gofr1 · Accepted Answer · 2016-09-28 18:02:04Z

2

You can do in this way create one variable query then form your query there with parameter . enter image description here

enter image description here

then goto dataflow properties , in expression select ODBC source.sqlcoomand and pass that variable here.

On first run will replace source query with the expression sql command. Thanks

edited Sep 28, 2016 at 18:02

gofr1

16k11 gold badges47 silver badges56 bronze badges

answered Sep 28, 2016 at 17:43

Aashish Jain

1215 bronze badges

Collectives™ on Stack Overflow

How to pass variable to sql query of ODBC Source in SSIS?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related