SSIS flow Sql query data to CSV to Oracle table in the same package

  • Hello All,

    Need a help in creating a new SSIS flow with below requirement- SSI package to be created which will first transfer the data from sql command to flat file csv. Then in the next step (same package), the same csv files to be loaded to DB table destination. Can it be done in the same package?

    My first step will contain the ole db source (sql command) and transferring the data to csv file using Flat file destination. Now for the second step, I need to pick Flat file source to load to data to db destination. Not sure how to link Flat file destination from first step to Flat file source from second step. Please assist.

     

     

    • This topic was modified 2 weeks, 1 day ago by  vibhuti.
  • That seems somewhat inefficient. I suggest that you add a Multicast after your OLEDB source to broadcast the same source data to both destinations in parallel. Here's a link to get you started.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • --Edit: removed duplicate reply.

    • This reply was modified 2 weeks, 2 days ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for replying. Initially I used the same MULTICAST method which is the best way. But as per the requirement, the csv file count & DB table destination count should be 100% in sync (Transactional data which gets modified every minute). So need to follow the data transfer in the sequence of sql query data--> csv data--> db destination. sorry I should have elaborated more earlier.

    I tried developing like this: Created data flow task1- sql server query data to flat file destination csv data & data flow task2- Flat file source to Oracle table (picks the same recent csv file from & transfer to Oracle table). task2 executing after task1 completion. But still I think as you suggested, its not an efficient way. currently testing this to check how it works. Any suggestion for the this  approach please.

    • This reply was modified 2 weeks, 1 day ago by  vibhuti.
  • Another approach would be to push the initial extract to a raw file, then use that as the source for your flat file and Oracle destinations.

    But I don't see why this would be any more bullet-proof than the Multicast idea, to be honest.

    Can you describe the possible problems you envisage with using Multicast and how your suggested approach overcomes them?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hello,

    We just need to ensure that the Oracle table data (generated from Oracle destination) & its count should be exactly same as csv data (generated from Flat file destination) & its count. That is why I was going with sql query data--> csv data--> Oracle table

    Pkg execution is taking 30 minutes to process 1 million records. If you can pls check the screen shot attached. Once I execute the pkg, the source data transfer is not in sync at any given point of time (Flat file having 37,092 rows transferred while oracle destination table has 24,728). Since this source data is transactional which keep on getting changed every minute, it can cause a mismatch between csv and oracle destination table data

    But your idea of 'initial extract to a raw file' now looks best. Can I store the sql query data into raw file or temp table first... then multicast that data to further to csv+oracle table

    • This reply was modified 2 weeks, 1 day ago by  vibhuti.
    Attachments:
    You must be logged in to view attached files.
  • while the package is running its normal that the row counts is different - writing to a file is a lot faster than loading to a remote oracle database.

    counts that matter are at the END of the processing - those should be exactly the same.

    taking 30 mins to process 1 million rows seems a bit too much - is that time taken on processing the source data or on transferring it to oracle?

  • Frederico's comments are spot-on.

    If it were me, I would attempt to resolve those warnings (exclamation mark inside yellow triangle). Something is misaligned and whatever it is may be slowing things down.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi All- Thanks for pointing that out. i am working on optimizing the execution. Also, for file count/data matching with table destination, I will insert my transactional data first in temp table & use that temp table as main source of data flow to file+ destination table

  • vibhuti wrote:

    Hi All- Thanks for pointing that out. i am working on optimizing the execution. Also, for file count/data matching with table destination, I will insert my transactional data first in temp table & use that temp table as main source of data flow to file+ destination table

    Temp tables do not work well with SSIS. I would suggest using a permanent table instead (which you TRUNCATE at the beginning of every execution).

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply