Issue with Execute SQL Task in Sequence container returning 0 results
-
I'm working on a package to update some local tables from a datalake we access. I've been able to write individual incremental update packages for each table. The general flow of the package is "Get Now" (UTC) - Get Max Modified Utc from local table, get list of changed ids from source table where modified > max modified and < now. This last step is set up as full result and saved in an object since it's used in a for each loop in the next step. When used in a standalone package, it works fine.
However, I'm trying to use sequence containers to run updates on multiple tables in parallel. The exact same setup always returns an object that has no records in it. I've verified all the queries, I've used a script task to view the "now" and "max modified" values to ensure they are generating properly, I've taken those valued and run the query in SSMS to ensure there are results returned, everything works as expected except for the SQL task inside the sequence container.
What could I possibly be missing here?
-
I do not know the answer to your question, but I do have a couple of questions about your solution architecture.
- Have you considered dumping all of the new/modified rows straight into a staging table and then running a stored procedure to do the UPSERTs?
- Are you also handling deletions?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Have you checked the scope of all the variables used? The right scope can have a big impact once you move to using containers.
If you have defined the same variable with different scopes they may not know each other and not share data. I've tried that. Lost some hair in the process. 🙂
- Phil Parkin wrote:
I do not know the answer to your question, but I do have a couple of questions about your solution architecture.
- Have you considered dumping all of the new/modified rows straight into a staging table and then running a stored procedure to do the UPSERTs?
- Are you also handling deletions?
I've considered that, but I'd still have to find the new / modified rows and that's what this query is supposed to be doing in order to avoid having to do the conditional split setup (I'm kind of new to SSIS) since some of the tables I have have a ton of columns to compare. As far as deletions, I don't have to worry about that. Everything is a logical delete.
- kaj wrote:
Have you checked the scope of all the variables used? The right scope can have a big impact once you move to using containers.
If you have defined the same variable with different scopes they may not know each other and not share data. I've tried that. Lost some hair in the process. 🙂
I have. As far as scoping goes, everything should be available in the right place. This is actually the only sequence container enabled right now, so there should be no other issues with other containers messing with the data. Once I get it going, I'm actually hoping they do not touch each other at all when they're scoped to just their sequence container as that could have some negative consequences to the way this runs.
- SoDakDave wrote:
I've considered that, but I'd still have to find the new / modified rows and that's what this query is supposed to be doing in order to avoid having to do the conditional split setup (I'm kind of new to SSIS) since some of the tables I have have a ton of columns to compare. As far as deletions, I don't have to worry about that. Everything is a logical delete.
Good news about the deletes.
If the first thing you do is dump all of the selected source rows into a staging table, you can do an UPDATE and INSERT from there. Why is there any need to compare lots of columns? Just match on PK and do the business.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Scope was fine. Namespacing was off. Separated that out and it's working like magic.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply