SQLTeam.com | Weblogs | Forums

How to pass variable in SSIS data flow source task

sql2008r2

#1

Hi all,
If any one knows how to use variable in SSIS data flow source task using OLE DB connection.kindly help me on this

Query in my data flow source task

declare @id int
set @id=?

select @id,column1 from my_tablename

The above query throwing the following error

TITLE: Microsoft Visual Studio

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.


ADDITIONAL INFORMATION:

Syntax error, permission violation, or other nonspecific error (Microsoft SQL Server Native Client 11.0)


#2

Hi Ambika,
Object variables in SSIS are incredibly versatile, allowing the storage of almost any type of data (even .NET objects).
Here I am writing some steps that may help, follow the given steps:
Step 1:
Create Variable with name Dt in SSIS Package.
Step 2:
In Data Flow Pane bring OLE DB Source and write query and then map parameter.
Final Output:
Now set the variable Dt to the date you want to pass as parameter to your query in OLE DB Source after this you can see the results by using Data Viewer.

You should go through this it may help yours http://www.select-sql.com/mssql/how-to-use-a-variable-inside-sql-in-ssis-data-flow-tasks.html


#3

HI Guys,

i have same problem and i want to call function in data flow task and it doesnt allow me to do it.

"SELECT * FROM [dbo].[itvfExportPayrollAurionBaptcaretest] @[User::StartDate] ,@[User::EndDate] "

it gave me option to use cmd from variable and it doesnt work

can you please help me with this