DataStage Vs SSIS

DataStage Vs SSIS

• Robert Franks

  In regard to my previous post:

  This is not out-of-the-box functionality in SSIS and I have complained about this before in this forum. Currently the only way to do this would be to write your own Dataflow Destination or write a script.

  Anyone, who has used another ETL tool (In my case it was Cognos DecisionStream) will know that these tools often have INSET/UPDATE as an option on the destination datasource. In presume that tools like Informatica and Datastage also have this functionality. It saves a lot of coding.

  My point is that this a a real annoyance for anybody migrating from another product and it is something that Microsoft should address.

  Has anybody else out there encountered this

  Michael Morrissey.

  
  

• MicahN

  Jeyaraj N wrote:

  Could you please provide driver details to use all the SSIS stages to connect to Oracle 9i.

  You need an OLE DB driver for Oracle. You get one of these for free when you install SQL Server - or you could go and hunt out some other ones (including one that Oracle have built themselves).

  -Jamie

  
  
  

• fisher_iso

  In regard to point 3

  You can perform this logic in the control flow by creating precedence constraints which contains conditional logic:

  trim(@[User::Variable1]) == "Value"

  In regard to point 5

  This is not out-of-the-box functionality in SSIS and I have complained about this before in this forum. Currently the only way to do this would be to write your own Dataflow Destination or write a script.

  Michael Morrissey.

  
  

• drfoxs13

  1. If I correctly understand the question, you need to use Control Flow - create a separate tasks that will be executed before and after Data Flow and connect them with precedence constraints to ensure correct order. Another way to achieve this is to place these Pre/PostExecute tasks in appropriate control flow Events.
  
  2. Depending on the complexity of the XML, the XML source adapter may work for you.
  
  3. Use precedence constrains with expressions.
  
  4. For control flow you may create a shared package and call it using Execute Package task from other packages.
  
  5. Take a look at SCD transform, if it works for you - or build similar data flow by splitting data using lookup component.
  
  6. Right click control flow, select Logging - the logging functionality is quite extensive.
  
  

• Tommy8890

  Michael Morrissey wrote:

  In regard to my previous post: This is not out-of-the-box functionality in SSIS and I have complained about this before in this forum. Currently the only way to do this would be to write your own Dataflow Destination or write a script. Anyone, who has used another ETL tool (In my case it was Cognos DecisionStream) will know that these tools often have INSET/UPDATE as an option on the destination datasource. In presume that tools like Informatica and Datastage also have this functionality. It saves a lot of coding. My point is that this a a real annoyance for anybody migrating from another product and it is something that Microsoft should address. Has anybody else out there encountered this Michael Morrissey.

  Hi Michael,

  Point taken and, yes, I've encountered this in Informatica. Like yourself I was initially disappointed that the same wasn't in SSIS until I realised that you can achieve the same, just in a different way, using the OLE DB Command.

  I have a question for you actually. When you have encountered this functionality in the past, did the deletes/updates occur

  1. a row at a time (i.e. a statement issued for every row in the pipeline)
  2. or was it a set-based update (i.e. a single statement issues for the whole lot)

  I know INFA does the former, as does SSIS when using the OLE DB Command.

  I have previously asked for a new destination adapter in SSIS that does set-based updates/deletes - although I'm not sure its at all possible without building a temporary table somewhere first.

  -Jamie

  
  
  

• jebrown

  Michael Morrissey wrote:

  In regard to point 5 This is not out-of-the-box functionality in SSIS and I have complained about this before in this forum. Currently the only way to do this would be to write your own Dataflow Destination or write a script. Michael Morrissey.

  I wouldn't really concur with this statement. To say it isn't out-of-the-box functionality infers that you cannot do it - and that isn't true. Witness here: http://www.sqlis.com/default.aspx 311

  -Jamie

  
  
  
• DataStage Vs SSIS