SSIS Parameter Mapping

Toggle sidebar Toggle sidebar
C

coder_t2

Member
Nov 6, 2009
92
0
0
I am learning how to set up Parameters for my SSIS Tasks. I kind of have the hang of it, but I am still running into weird issues. I am running a query like below on a Oracle server through SSIS. However, when I create the parameter for the drugname, nothing gets inserted into the temptable. The Job does complete successfully. The variable is of type string and the paramater is of type varchar. So I am not sure why it isn't working. Any ideas? BTW, I can get this to work fine when I do the date as a parameter. Its just the drugname that is giving me grief. Thanks.

insert into tempTable
select *
from table t
join table2 t2 on
t2.drugid = t.drugid
where t.date = to_date('05-01-2010', 'YYYY-MM-DD')
and t2.drugname = ?
 
K

KIAman

Diamond Member
Mar 7, 2001
3,342
23
81
First thing I would check is that the string is not passing any special characters or spaces.
 
O

Oyster

Member
Nov 20, 2008
151
0
0
Also, refer to this post - it has some screenshots to help you through the process: http://decipherinfosys.wordpress.co...ed-sql-commands-using-the-execute-sql-task-i/

If the data types do not match, you'll get a NULL in your resultset. As a side note, successful completion of the task does not indicate expected results, especially in SSIS.

At the end of the Execute SQL Task, add a script task and log your results - I usually do a MsgBox and view the variables or table contents.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
Still not able to figure out. I get an error saying that no results were returned and that the variable for the resultset could not be set...

I know its returning nothing, I need to figure out why. There are no special characters in the variable value. The variable datatype is set to string, and the parameter datatype is set to varchar, so they are compatible...
 
K

KIAman

Diamond Member
Mar 7, 2001
3,342
23
81
Can you run this query in a directly and put in a value to match a drugname you are trying to find?

If that works, then (assuming the variable passed is good), the only thing that could be wrong is how the parameters were built in your SSIS package.

You could also turn your query into a stored procedure

Code: 
create procedure IDoSomethingKewl
    @DrugName varchar(50)
as
begin

  insert into tempTable
  select *
  from table t
  join table2 t2 on
  t2.drugid = t.drugid
  where t.date = to_date('05-01-2010', 'YYYY-MM-DD')
  and t2.drugname = @DrugName

end

Then for your SSIS package, do this

Code: 
exec IDoSomethingKewl @DrugName = ?

And make sure you name your parameter to @DrugName in your SSIS package.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
Yes it works when run directly. And it works as a stored procedure. However, I am accessing a Oracle database with this task. So using a stored procedure doesn't help my case since I am back to using openqueries, which is what I was trying to avoid.

I have read in a couple places that issues sometimes arise from using SSIS parameters when connected to Oracle. Anyone else have experiences like this?
 
O

Oyster

Member
Nov 20, 2008
151
0
0
Sorry if I missed this, but are you trying to return a table from Oracle using a SPROC?

I know a hack we use for SSIS to be able to use SPROCs from within SQL Server, but never tried it with Oracle. I can disclose the details if you confirm the above.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
No, unless I misunderstood you oyster. I used to use openqueries and stored procedure to access all my info from Oracle. But I have since switched to using SSIS tasks, since they are more efficient. However, sometimes I will run several queries directly on the Oracle server, and just create tables on the Oracle server. I do this because of different automated tasks I have set to run.

So the question is, could there be an issue with converting the variable to varchar and the actual field being varchar2 on Oracle?
 
O

Oyster

Member
Nov 20, 2008
151
0
0
Any varchar variants should convert to strings - but every setup is different. I have no clue as to how your collation is set and how your Oracle server is configured. That's why I mentioned earlier that you should log the data flowing back and forth.

Something like this is really simple to debug. Monitor the query executed on the Oracle side and tap into the Pre* and Post* events in the debugger for SSIS to see what's happening to your variable...
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
Sorry, I am very new to using these tables. I am much more of a programmer than a database guy, so all these tools are new to me. I am limited on access to the Oracle server. I just don't have decent applications to run monitoring stuff.

How do I debug the SSIS? And where do I see values? I keep trying to treat it like its code, but it doesn't seem to show me what I am looking for.
 
O

Oyster

Member
Nov 20, 2008
151
0
0
Maybe these will help:

http://msdn.microsoft.com/en-us/library/ms141754.aspx
http://msdn.microsoft.com/en-us/library/ms140274.aspx
http://msdn.microsoft.com/en-us/library/ms140033.aspx

You have done half the work... think of it as a challenge. If you do this, you'll be empowered, man :).

Oh, and SSIS is a developer tool, caveat being that you need to understand data at a higher level - not necessarily at the DBA level (I am assuming you have someone you can seek help from).

Outside of that, refer to Technet and MSDN - they have tons of information on SSIS.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
The links helped me get a little more knowledge, but still trying to figure out what SSIS is passing to the Oracle database. If it just passing a empty string or what. Is there anyway to check how the query looks with the variable in place?
 
O

Oyster

Member
Nov 20, 2008
151
0
0
I remember doing this a long time ago on Oracle 10g, but I may be off-base. You may want to hunt for exact details elsewhere. In any case, put your SSIS package in the debug mode and create breakpoints on the Pre* and Post* events of your Execute SQL task (or OLE DB Source DF). Halt at the Pre* event, go to your Oracle Enterprise Manager > InstanceName > Sessions and look for the SQL for each session. Depending on the number of open sessions, you may have to play around with pinning down your specific sessions, but that should tell you what SSIS is sending to Oracle.

If there are parameters in your SELECT, I wouldn't be surprised if SSIS is executing the query twice. The first time is for fetching the metadata and second time is for fetching the actual data (I know this is what it does on SQL Server).

In general, this is the same process that you'd have to go through to debug your SQL Server queries.


EDIT: If you need more details look up SQL Trace and TKProf:
http://www.orafaq.com/wiki/SQL_Trace
http://www.orafaq.com/wiki/TKProf
 
Last edited:
C

coder_t2

Member
Nov 6, 2009
92
0
0
Ok I figured out what was going on. Some invisible character was getting tacked onto the end of the variable, probably a line feed. I could fix it by doing the substr function and removing the last character. But I found an even better way. I switched to using an ODBC connection, instead of a OLE DB. And I had to set the variable to WVARCHAR, since WVARCHAR is Unicode, and that is what the Oracle server is set to.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
Cool. Sadly it looks like its only for 2008. I use Sql Server 2005. Is there anything that is good for Bulk Inserting into SQL Server from Oracle? Replication is proving to be a giant pain in the arse currently.
 
O

Oyster

Member
Nov 20, 2008
151
0
0
You could use SSIS for that. Export data to flat files from Oracle (or to a staging DB, if you follow a data warehouse methodology) and then use the bulk insert task from within SSIS (SQL Server Destination in your DF). It'll allow you to configure the batch size, cache allocation, etc.

There are always compromises, and the one here is that it may not be as reliable as your replication at the engine level. Nonetheless, as soon as you achieve conformity across all your data, the chances of a SSIS package failing are pretty minimal. If your clients/bosses are happy with 95% success, go down this route.

This by no means is as simple as it sounds - you have to ensure that source is properly indexed, has its indexes up-to-date, etc. Because if you're processing tons of data, you have a SSIS package that is running every half-an-hour, and your indexes are whacked, then your copying process will never be able to keep up.

On the other hand, if you're copying data on a nightly basis, and the amount of data is manageable, then I'd definitely use SSIS.
 
C

coder_t2

Member
Nov 6, 2009
92
0
0
Yea. I'll stick with SSIS. I am talking about replication of 20+ million rows. So alot of data to be passing into a flat file.
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom