September 27, 2019 at 4:29 pm
I have a job that runs automatically every night that updates Table A from Table B.
Last night we had a network hiccup, and only 10 records made it into Table A from Table B.
Question: How do I tell the job to only update Table A, if it has all the records from Table B, otherwise, dont update ?
Thank you !
September 27, 2019 at 4:42 pm
What is the structure of your SSIS package?
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.
September 27, 2019 at 4:52 pm
Hmm, not sure what you mean by structure, but it's very simple: Its a data flow task in SSIS, where all columns in Table A are populated from Table B rows.
My goal is that Table A is ONLY updated if it has all rows from Table B
Last night, only 10 out of 1000 records were put into Table A, due to the network having a hiccup and losing connection between Table B (which is found in another database).
September 27, 2019 at 5:24 pm
Are you using an OLEDB destination in your data flow?
What are the values of 'Rows Per Batch' and 'Maximum Insert Commit Size'?
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.
September 27, 2019 at 6:03 pm
My general approach to this type of problem is:
If there is a network error - then step 2 will fail and the load to the final table will not be executed. In the code for step 3 - this is performed in an explicit transaction and if an error occurs during the load process it is rolled back leaving the destination table in the same state it was prior to attempting the load process.
I also include in the load procedure steps that disable non-clustered indexes on the final table, an index rebuild after the new data has been loaded and a final step to truncate the stage table.
The load process can be built using MERGE to handle inserts/updates/deletes in a single transaction - or you can build separate insert/update/delete steps in the explicit transaction, whichever work best for you.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 27, 2019 at 7:56 pm
Thanks Jeffrey. One question: why even have step 2 ? Cant you just have the stored procedure get data from original source ? If a network error occurs, a rollback occurs and leaves destination table as is ?
September 27, 2019 at 8:08 pm
Can you wrap the entire job in a TRY CATCH blocks?
In the try block begin and commit the transaction
In the catch block rollback and re-throw the error.
September 27, 2019 at 9:14 pm
Can you wrap the entire job in a TRY CATCH blocks?
In the try block begin and commit the transaction
In the catch block rollback and re-throw the error.
I don't think that TRY/CATCH is available in SSIS.
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.
September 28, 2019 at 12:46 am
Jonathan AC Roberts wrote:Can you wrap the entire job in a TRY CATCH blocks?
In the try block begin and commit the transaction
In the catch block rollback and re-throw the error.
I don't think that TRY/CATCH is available in SSIS.
It is if you are executing the script as an SSIS SQL Task
September 28, 2019 at 4:27 pm
Thanks Jeffrey. One question: why even have step 2 ? Cant you just have the stored procedure get data from original source ? If a network error occurs, a rollback occurs and leaves destination table as is ?
Because the stored procedure would have to pull the data across a linked server - which introduces a whole other layer of issues for the process.
By using a staging table - your process can perform extra validation and verification of the data before loading to the final table (if needed).
Can you wrap the entire job in a TRY CATCH blocks?
In the try block begin and commit the transaction
In the catch block rollback and re-throw the error.
This would only be available if you use a data flow to move the data from the source to a staging table - then use a stored procedure to load the data to the final table. I would recommend using TRY/CATCH in that procedure to handle any errors and rollback...but if you have a single statement (e.g. MERGE) then an explicit transaction wouldn't be necessary as any errors would roll back the full insert/update/delete.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 28, 2019 at 5:55 pm
Jonathan AC Roberts wrote:Can you wrap the entire job in a TRY CATCH blocks?
In the try block begin and commit the transaction
In the catch block rollback and re-throw the error.
This would only be available if you use a data flow to move the data from the source to a staging table - then use a stored procedure to load the data to the final table. I would recommend using TRY/CATCH in that procedure to handle any errors and rollback...but if you have a single statement (e.g. MERGE) then an explicit transaction wouldn't be necessary as any errors would roll back the full insert/update/delete.
I hadn't read the information about the source table being on another database. A good way to go is to first copy the data onto a Staging table on the target database that has the same structure as the source table. This should be fast as it is only copying 1000 rows across so less likely to fail with a network error, the batch size of the insert should also be set to a value larger than the number of rows on the table so it's done in one step. If it does fail the restart instructions would be to truncate the target staging table then rerun from the failed step.
September 29, 2019 at 3:31 pm
I hadn't read the information about the source table being on another database. A good way to go is to first copy the data onto a Staging table on the target database that has the same structure as the source table. This should be fast as it is only copying 1000 rows across so less likely to fail with a network error, the batch size of the insert should also be set to a value larger than the number of rows on the table so it's done in one step. If it does fail the restart instructions would be to truncate the target staging table then rerun from the failed step.
Be careful with the batch and commit sizes - especially the commit size. If this is too large and there are millions of rows you can easily cause the transaction log to fill a drive and the process will fail. This can also cause the load to take longer - as committing millions of rows in a single transaction can take a lot longer.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 30, 2019 at 9:41 am
Jonathan AC Roberts wrote:I hadn't read the information about the source table being on another database. A good way to go is to first copy the data onto a Staging table on the target database that has the same structure as the source table. This should be fast as it is only copying 1000 rows across so less likely to fail with a network error, the batch size of the insert should also be set to a value larger than the number of rows on the table so it's done in one step. If it does fail the restart instructions would be to truncate the target staging table then rerun from the failed step.
Be careful with the batch and commit sizes - especially the commit size. If this is too large and there are millions of rows you can easily cause the transaction log to fill a drive and the process will fail. This can also cause the load to take longer - as committing millions of rows in a single transaction can take a lot longer.
Yes, there has to be enough space on the transaction log to store all the necessary data, so the OP would have to make sure there is enough disk space to do this. I'm not sure it will take any longer to commit the transaction, a commit is normally very fast.
October 1, 2019 at 6:56 pm
Thank you everybody. I will play around with a staging table and some other ideas !
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply