November 17, 2022 at 7:03 am
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.
November 17, 2022 at 8:35 am
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.
November 17, 2022 at 8:36 am
--Edit: removed duplicate reply.
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.
November 18, 2022 at 6:52 am
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.
November 18, 2022 at 8:40 am
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.
November 18, 2022 at 10:47 am
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
November 18, 2022 at 12:37 pm
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?
November 18, 2022 at 2:00 pm
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.
November 21, 2022 at 5:24 pm
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
November 22, 2022 at 1:02 pm
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.
October 16, 2023 at 9:33 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply