http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Look 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. http://ayende.com/2292/ssis-integration-woes#comment9http://ayende.com/2292/ssis-integration-woes#comment9Thu, 12 Apr 2007 21:00:24 GMT> you lose all that drag and drop GUI that you fancy so much *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. http://ayende.com/2292/ssis-integration-woes#comment8http://ayende.com/2292/ssis-integration-woes#comment8Sun, 08 Apr 2007 08:16:25 GMTAs 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… http://ayende.com/2292/ssis-integration-woes#comment7http://ayende.com/2292/ssis-integration-woes#comment7Sun, 08 Apr 2007 07:49:55 GMTI 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. http://ayende.com/2292/ssis-integration-woes#comment6http://ayende.com/2292/ssis-integration-woes#comment6Sat, 07 Apr 2007 00:28:37 GMTThats 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. http://ayende.com/2292/ssis-integration-woes#comment5http://ayende.com/2292/ssis-integration-woes#comment5Fri, 06 Apr 2007 15:44:58 GMTHi 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. http://ayende.com/2292/ssis-integration-woes#comment4http://ayende.com/2292/ssis-integration-woes#comment4Fri, 06 Apr 2007 08:41:58 GMTIt is a hack, a mighty big one at that. I don't like that at all! http://ayende.com/2292/ssis-integration-woes#comment3http://ayende.com/2292/ssis-integration-woes#comment3Thu, 05 Apr 2007 17:47:45 GMTI 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. http://ayende.com/2292/ssis-integration-woes#comment2http://ayende.com/2292/ssis-integration-woes#comment2Thu, 05 Apr 2007 17:46:18 GMTGuess 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). http://ayende.com/2292/ssis-integration-woes#comment1http://ayende.com/2292/ssis-integration-woes#comment1Thu, 05 Apr 2007 16:41:09 GMT