Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

919 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

12 Experts available now in Live!

Get 1:1 Help Now