SSIS, how to use a parameter in the query for a connection
Posted on 2013-10-28
I'm trying to load data from Point A to Point B, overall this is straightfoward, but I need help in crafting the steps. I know what I want to do but this is the first time for a job of this sort in SSIS.
One big thing is that the Oracle schema owners in the source data are not the same, they are: Pharmacy_Dev, Pharmacy_Test, Pharmacy_Prod. So I assume this is a parameter to the job when it is called from Sql Agent. If so, then how do I receive this parameter (and use in a query)
So I'm moving data from
There's virtually no transformation, just straight moves, but I need to understand how to best do this in SSIS.
Step 1: Retrieve the date-time stamp of the last time data was loaded
(Select max(pull_date) from My_control_table where process = 'Pharmacy'
- do this first in "Execute Sql Task", place in variable
(this is done and working)
Step 2: Retrieve header info based on the above variable, how do I do that ?
In my data flow task, here's my query:
select [this and that]
from pharmacy_dev.phar_hdr ph
where insrt_tmstp > to_date([my_variable],'yyyy-mm-dd hh24:mi.ss')
and source_cd = '212'
my_variable = varPullFromDtTm
plus the schema name (pharmacy_dev) needs to be a parameter.
Also, I did a simple Derived Column step, and I created "Insert_Id" as RTIM("SSIS"), and the DataType jumped to "Unicode string [DT_WSTR]". Then SSIS croaked and said "Validation error. Get Pharm Header info from DA: Get Pharm Header info from DA: Columns "Insert_Id" and "AUD_INSRT_ID" cannot convert between unicode and non-unicode string data types.", so I am not sure what to do about this.