?
Solved

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

Posted on 2013-10-28
5
Medium Priority
?
420 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
[X]
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
  • 3
5 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

719 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