SSIS Buffer Error

Question

SSIS Buffer Error

Viewing 12 posts - 31 through 41 (of 41 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?

There would be properties that would be named FailPackageOnFailure or similar...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 2

skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops

Any chance your query against the Oracle source is timing out due to a setting on the ODBC connection?

EDIT: Or in the connection manager ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 3

sgmunson - Wednesday, May 9, 2018 1:32 PM
skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops
Any chance your query against the Oracle source is timing out due to a setting on the ODBC connection?

Where would I look for that? Is there something specific I should look for in the ODBC connection? You could be on to something because my other packages that are much more complex run no problem, but they are not using the Oracle DB as a source. this is the first package I have tested where I have this issue, and oracle is the source

sgmunson SSC Guru Points: 110640 More actions · Answer 4

skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops

Also wondering if you have the latest Oracle provided ODBC driver...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 5

sgmunson - Wednesday, May 9, 2018 1:47 PM
skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops
Also wondering if you have the latest Oracle provided ODBC driver...

Let me get this info and find out. I'll let you know. I need to contact my client for this info

sgmunson SSC Guru Points: 110640 More actions · Answer 6

skaggs.andrew - Wednesday, May 9, 2018 1:34 PM
sgmunson - Wednesday, May 9, 2018 1:32 PM
skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops
Any chance your query against the Oracle source is timing out due to a setting on the ODBC connection?
Where would I look for that? Is there something specific I should look for in the ODBC connection? You could be on to something because my other packages that are much more complex run no problem, but they are not using the Oracle DB as a source. this is the first package I have tested where I have this issue, and oracle is the source

If connection pooling is enabled, there could be a pool timeout. Once you look there, be sure to check out both the 32-bit and 64-bit versions of this to be sure you find the correct ODBC connection.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 7

sgmunson - Wednesday, May 9, 2018 2:14 PM
skaggs.andrew - Wednesday, May 9, 2018 1:34 PM
sgmunson - Wednesday, May 9, 2018 1:32 PM
skaggs.andrew - Wednesday, May 9, 2018 12:44 PM
skaggs.andrew - Wednesday, May 9, 2018 12:34 PM
sgmunson - Wednesday, May 9, 2018 11:05 AM
skaggs.andrew - Wednesday, May 9, 2018 10:36 AM
sgmunson - Wednesday, May 9, 2018 10:30 AM
skaggs.andrew - Wednesday, May 9, 2018 8:53 AM
I tried making updates to the precedence constraint to test out the package. It still executed in the same manner. However I then just manually ran the first Data Flow Task in the container. In this data flow task, I have just one source -> target flow. I attached screen shots. This makes me think it isnt the precedence constraint. Thoughts? Thanks
Please be more specific. What was the precedence constraint for package success? How, exactly, did you change it? If you attached any new screen shots, I don't see them, and I've already looked at the ones you posted earlier. I still suspect a precedence and/or package success constraint that is misconfigured in some way. Seems to me if there were a bug that could cause this kind of problem, someone else would have already found it. And I've seen this kind of problem before. If the package success is dependent on only one thing, and that one thing can finish before the rest of the elements of the package, then those other things that aren't done yet get abandoned and the package ends. You have to be rather careful when setting up package success constraints, to ensure you DON'T run into this problem.
Sorry, here are the screen shots
In your Incident Tables container, I'd be looking to see the actual precedence constraints, and for ALL of the elements within it. I'm also interested in any package success constraints. Need the details...
As of now, I have all of the precedence constraints between data flow tasks setup as the following:
Evaluation Operation = Constraint
Value = Completion
In the beginning, all of the values were set to "Success". I tried switching to see if that would fix it. It is stopping on the very first data flow task that has just 1 process "source => target"
I don't see any constraints inside the data flow task. Are there constraints here as well?
Just to test, I created an all new package. I have 1 data flow task. Inside the task I created a source and target. I'm pulling from my clients Oracle DB and doing a straight load into the Temp database in SQL server. There is nothing else at all in this package. I ran it and it's stopping roughly at the exact same amount of rows loaded. It hasn't completed, but the message below says its complete. I attached a file so you can see. Do you think maybe its not the constraints? I'm lost as to why it just abruptly stops
Any chance your query against the Oracle source is timing out due to a setting on the ODBC connection?
Where would I look for that? Is there something specific I should look for in the ODBC connection? You could be on to something because my other packages that are much more complex run no problem, but they are not using the Oracle DB as a source. this is the first package I have tested where I have this issue, and oracle is the source
If connection pooling is enabled, there could be a pool timeout. Once you look there, be sure to check out both the 32-bit and 64-bit versions of this to be sure you find the correct ODBC connection.

Here is what I have for both 32-bit and 64-bit

sgmunson SSC Guru Points: 110640 More actions · Answer 8

Well, it doesn't appear that you have the connection pooling enabled, but I'd go through the actual connection configuration and see if there's any settings in there that could cause a timeout. Similarly, I'd check the properties of the connection manager in SSIS for a timeout value as well.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246965 More actions · Answer 9

Apologies if you've already answered this question, but is the package deployed to SSISDB?
If so, can you check the 'All Executions' report for the package to see whether that gives you any clues as to what is happening?

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

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 10

Phil Parkin - Thursday, May 10, 2018 9:43 AM
Apologies if you've already answered this question, but is the package deployed to SSISDB?
If so, can you check the 'All Executions' report for the package to see whether that gives you any clues as to what is happening?

Hi Phil, could you help explain to me how I access the "All Executions" report? I have never done this. Thanks

Phil Parkin SSC Guru Points: 246965 More actions · Answer 11

skaggs.andrew - Thursday, May 10, 2018 10:36 AM
Phil Parkin - Thursday, May 10, 2018 9:43 AM
Apologies if you've already answered this question, but is the package deployed to SSISDB?
If so, can you check the 'All Executions' report for the package to see whether that gives you any clues as to what is happening?
Hi Phil, could you help explain to me how I access the "All Executions" report? I have never done this. Thanks

Yeah, it's easy enough.

Navigate to the package in SSMS, starting at the Integration Services Catalogs node.
Right click the package, select Reports/Standard Reports/All Executions

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

skaggs.andrew SSCrazy Points: 2476 More actions · Answer 12

Phil Parkin - Thursday, May 10, 2018 12:39 PM
skaggs.andrew - Thursday, May 10, 2018 10:36 AM
Phil Parkin - Thursday, May 10, 2018 9:43 AM
Apologies if you've already answered this question, but is the package deployed to SSISDB?
If so, can you check the 'All Executions' report for the package to see whether that gives you any clues as to what is happening?
Hi Phil, could you help explain to me how I access the "All Executions" report? I have never done this. Thanks
Navigate to the package in SSMS, starting at the Integration Services Catalogs node.
Right click the package, select Reports/Standard Reports/All Executions

Hi Phil,
I have checked the report, but I don't really see anything here that is pointing me in a different direction as to what the issue could be. Any other thoughts?