August 21, 2018 at 6:55 am
I am very new to SSIS and I am just trying to understand if there is a way to create multiple staging tables on a fly. So this is what happens. We get requests from users and they give us a file which contains records 50k, 100k or sometimes more and all they users want to know if those contacts exists in our main table or not. We have some staging table which we truncate, insert data into it from the source, match the data in that staging table against our main table and then we generate a file for end users. One request with 50k records takes roughly 30 minutes to an hour because we use contact verification tool to standardized the detail, generate city, zip, county and then match against our main table .Now this might not be a good practice because we are unable to process multiple request in parallel because we are only using 1 staging table. Is it possible to create tables per session and give it a name with Date and time so we can handle multiple requests without truncating the only table? If so then what are steps I can take? Let me know if my question confuses anyone?
August 21, 2018 at 7:11 am
NewBornDBA2017 - Tuesday, August 21, 2018 6:55 AMI am very new to SSIS and I am just trying to understand if there is a way to create multiple staging tables on a fly. So this is what happens. We get requests from users and they give us a file which contains records 50k, 100k or sometimes more and all they users want to know if those contacts exists in our main table or not. We have some staging table which we truncate, insert data into it from the source, match the data in that staging table against our main table and then we generate a file for end users. One request with 50k records takes roughly 30 minutes to an hour because we use contact verification tool to standardized the detail, generate city, zip, county and then match against our main table .Now this might not be a good practice because we are unable to process multiple request in parallel because we are only using 1 staging table. Is it possible to create tables per session and give it a name with Date and time so we can handle multiple requests without truncating the only table? If so then what are steps I can take? Let me know if my question confuses anyone?
There is a possible way round this. If you have deployed your package to SSISDB, you will notice that every time you run the package, a unique 'run number' is created. This is held in a system variable called ServerExecutionId (from memory). If you add this column to your staging table, along with a Boolean IsProcessed column, you'll be able to
1) DELETE WHERE IsProcessed = 1
2) Process multiple batches concurrently, based on ServerExecutionId.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply