Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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

808 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