SSIS Integration Woes
I am not sure if it is me or SSIS, but something is very wrong here. I just spend an ungodly amount of time trying to solve this simple scenario:
- Source database is Oracle, Destination database is SQL Server
- A table with large amount of rows
- The table has a LastModifed field
- Every few minutes, an SSIS process should wake and grab the latest updates from the table, then update a state table, with the last updated date, for the next run
It is something that should take minutes to handle with code, but even now, I just can't make it happen. The major issue here is very simple, I can't pass parameters to the query. The OLE DB Source won't accept parameters when using Oracle, and when using the DateReader source, it simply has no way to pass input parameters that I can find.
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
ADDITIONAL INFORMATION:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)
I am not an SSIS expert, but I consider myself technically able, and I certainly knows how to search google. No answer on this one. I went and wrote the logic in an Oracle stored procedure, then I faced another interesting issue. There doesn't seem to be a way to call an Oracle SP from SSIS.
SSIS supposed to stand for Integration, and while I wouldn't assume that it would integrate with the DB engine that I wrote last month in 2AM, I don't think that Oracle is such a minor player that integration with it should be so hard.
Please, someone, make my day and show me that I am stupid, because I am very close to throwing the whole thing out and writing it in an envrionment that doesn't force me to jump through hops just to get the simplest things done. (And have much better error reporting, as an aside)
Comments
Guess you'll need to resort to this:
http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html
I agree SSIS is pretty clunky at times (and debugging an SSIS package is as much fun as removing your fingernails with a hammer and a chisel).
I used that same method that Tomas pointed to and it worked great for me. It does feel kind of like a hack though and I do really wish that it was easier. It was much easier with dts packages to connect to an Oracle database and pass parameters.
It is a hack, a mighty big one at that.
I don't like that at all!
Hi Ayende,
When using SSIS, check your OO-compulsion at the door. You must forget OOP when using SSIS. Throw reusability out the door too. When you're ready, open up that package. SSIS was not designed for software devs, it's supposed to be made for DBAs with little or no programming experience.
Tomas' recommendation is the way to go. NO it's not a hack, at least not in SSIS' perspective. Sometimes it's the only way you can do it. Scratch that -- MOST OF THE TIME it's the only way to do it.
You'll just have to swallow it.
Thats why I always think writing our own SSIS like stuff will get money. Its really horrible, I have used that once before, and its hell too buggy to be usable indeed. I remember I come across something like case sensitivity stuff when I create a task and it takes me hell day long to figure out whats really happening.
If your task is possible to be handled with coding and you are comfortable with it, dont force yourself with SSIS, its just not going to be a nice marriage.
I apologize for not being a 100% detailed here, but I am pretty sure that the issue is with the OLEDB provider. Under OLEDB, you pass parameters differently than with a SQL connection.
I think it's something like SELECT * FROM Table WHERE ModiefiedDate >= ?
and then you need to name your parameter either 0 or 1 (I forget if it's 0 based....)
There are also all kinds of issues with using Dates with that setup.
As I have had a little unpleasant encounter with your dear friend the SSIS, I would recommend that you keep it as simple as possible.
Ultimately what I’ve done was to create a linked server to Oracle, created a SP that ran queries against it, you can either write them as
SELECT * FROM OracleDb.Oren.MyTable (Better if works…)
Or using the OPENQUERY(OracleDb, “SELECT * FROM Oren.MyTable”) function.
After you have the results remember that the CROSS APPAY can join UDFs with tables.
When all is done, create a job to run the SP every couple of minutes (SSIS does the same…) and you are good to go.
Obviously, you lose all that drag and drop GUI that you fancy so much, but I never considered you as one that falls for that kind of magic…
cough
WHAT?! I fancy drag & drop?
The reasoning behind using SSIS for this is that there I can't use a linked server between the two databases (firewall).
The other reasoning is that this way I wouldn't have to build my own error handling, reporting, integration with monitoring, etc.
Look at SlowlyChangingDimension transformation component... it handles the versioning of records. Use two date columns - one for added date and one for expired date.
Then, create a view where you select where expired date is null for the current view.
Comment preview