Solved

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

Posted on 2013-10-28
5
401 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now