February 24, 2015 at 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 ?
February 24, 2015 at 4:34 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2015 at 4:36 am
Did you mean OLEDB Command, by the way?
OLEDB Destination does not have a SQLCommand.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2015 at 4:47 am
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
February 24, 2015 at 4:53 am
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>
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2015 at 5:42 am
hmm,
Looks like I have to use hardcodes table names in sql command in OleDB Destination
February 24, 2015 at 5:48 am
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
February 24, 2015 at 5:51 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 24, 2015 at 5:58 am
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
"
February 24, 2015 at 6:16 am
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
March 24, 2019 at 2:53 pm
er.mayankshukla - Tuesday, February 24, 2015 4:19 AMHi 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply