March 4, 2016 at 1:07 pm
Hey guys, can someone point me in the right direction on how to create a sql command in an ssis variable and pass that into an Attunity Oracle data source? I need to be able to set a date in the where clause 5 days from my last run date.
Thanks
March 5, 2016 at 2:48 am
dndaughtery (3/4/2016)
Hey guys, can someone point me in the right direction on how to create a sql command in an ssis variable and pass that into an Attunity Oracle data source? I need to be able to set a date in the where clause 5 days from my last run date.Thanks
Building the SQL statement as a string and passing it to Oracle implies there is no way to map SSIS Variable to a parameter of a SQL statement with Attunity which would be the preferred way to create the SQL command, at least with SQL Server.
Assuming parameters are not supported and you want to build the SQL statement in a Variable:
Not sure your level so will try to start from the beginning...
1. In your SSIS Package create a new Variable of type String.
2. Configure the Variable to takes its value from an Expression by setting property EvaluateAsExpression to True.
3. Set the Expression value to something like:
="SELECT something FROM schema.table_in_oracle_db WHERE some_date > '" + (DT_WSTR,100)[User::MyRunDate] + "'"
4. Setup your Task or Component that connects to Oracle to execute the SQL in the Variable.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2016 at 7:29 am
Yeah, I have steps 1 through 3 complete. Just not sure how to go about setting up step 4
March 7, 2016 at 7:33 am
First Bing result took me to SO
then to this nice blog about Data Flow source, but the same approach applies
http://sornanara.blogspot.com/2013/11/ssis-use-dynamic-sql-in-oracle-source.html
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply