SSIS OleDB Destination Sql Command From Variable

Question

SSIS OleDB Destination Sql Command From Variable

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
February 24, 2015 at 4:19 am

#295448

Hi all,
I need to put a dynamic query to OleDB destination SQL command (Data access mode)
But I don't find any option for that.
Lets say I have my query in the variable [user::variable1].
How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

er.mayankshukla (2/24/2015)
Hi all,
I need to put a dynamic query to OleDB destination SQL command (Data access mode)
But I don't find any option for that.
Lets say I have my query in the variable [user::variable1].
How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

Can't be done, as far as I know. This is set at design time.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 2

Did you mean OLEDB Command, by the way?

OLEDB Destination does not have a SQLCommand.

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

Phil Parkin (2/24/2015)
Did you mean OLEDB Command, by the way?
OLEDB Destination does not have a SQLCommand.

Well, it does have one, but I don't think it's actually used. (or actually useful)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247158 More actions · Answer 4

Silly me. I did not edit the source XML to check

To save others the bother, here is what it looks like (SSDT 2013):

<property

dataType="System.String"

description="The SQL command to be executed."

name="SqlCommand"

UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor"></property>

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 5

hmm,

Looks like I have to use hardcodes table names in sql command in OleDB Destination

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 6

er.mayankshukla (2/24/2015)
hmm,
Looks like I have to use hardcodes table names in sql command in OleDB Destination

I'm curious, how exactly do you use a SQL statement in the OLE DB Destination?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247158 More actions · Answer 7

er.mayankshukla (2/24/2015)
hmm,
Looks like I have to use hardcodes table names in sql command in OleDB Destination

The table name in the OLEDB Destination component is a property.

I can't see what SQLCommand has to do with anything: please explain.

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 8

I was planning to use the temp table as Destination.

But to preserve the metadata while mapping I was thinking of using this expression in SqlCommand Data access mode of OleDB Destination

"IF OBJECT_ID('tempdb..#cache_tbl') IS NULL

begin

select * from 'cache.tbl'

end

else

begin

select * form #cache_tbl'

end

"

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 9

That's probably not going to work.

Your temp table has to exist when you are designing the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

afzaki7 Newbie Points: 7 More actions · Answer 10

er.mayankshukla - Tuesday, February 24, 2015 4:19 AM
Hi all,I need to put a dynamic query to OleDB destination SQL command (Data access mode)But I don't find any option for that.Lets say I have my query in the variable [user::variable1].How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

Check this
SSIS Dynamic SQL Command

SSIS OleDB Destination Sql Command From Variable

Cookies on SQLServerCentral