Solved

SSIS, how to use a parameter in the query for a connection

Posted on 2013-10-28
5
407 Views
Last Modified: 2016-02-11
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

Pharmacy_Hdr
   Pharmacy_Detail
        Pharmacy_Ingredient
Pharmacy_Trlr

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.
0
Comment
Question by:Alaska Cowboy
  • 3
5 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 39609311
"the Oracle schema owners in the source data are not the same"


What I would do is add linked server in SQL Server to ORACLE so you can use the full four name qualifier in your all queries inside or outside SSIS. I believe SQL has now a native ORACLE OLEDB that you can select to add this linked server then all will become much easier.

Step by step details below:
http://support.microsoft.com/kb/280106
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39609482
>>add linked server in SQL Server to ORACLE so you can use the full four name qualifier in your all queries inside or outside SSIS.
- I don't understand what you are saying. I have a connection to pharmacy_dev (Oracle) and I can retrieve data. So the connection is there.
- is there a way to send a parameter to the package with the schema name, then the query uses this parameter as the scheme ? e.g. "select * from var_schema_name.phar_hdr"

I also need to know how to use a parameter in a query, e.g.,
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'
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39613064
actually, I got my answer while at work, so I'm ok on this. Thanks anyway.
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 39613067
this didn't really answer my question and I was able to find the answer elsewhere. But thanks for the tip.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question