January 8, 2016 at 8:55 am
Hi All
I have a SSIS dataflow task that pulls data from my staging table, this table for example has 315000 rows.
I run the job which is a basic select * but it produces varying results each time.
has anyone else experienced this before?
January 8, 2016 at 9:02 am
what do you mean by varying results ? is the data the SSIS touches absolutely static(ie a staging table only you touch, with no joins to other data), or are you querying a transactional database that is getting updates/inserts/deletes?
is there a WHERE clause involved at all, or a view? any functions?
can you explain what the issue is?
Lowell
January 8, 2016 at 10:48 am
Sl33py (1/8/2016)
Hi AllI have a SSIS dataflow task that pulls data from my staging table, this table for example has 315000 rows.
I run the job which is a basic select * but it produces varying results each time.
has anyone else experienced this before?
How do you know that there is a problem? How are you doing the row count? Comparing source / target counts? We need to know a bit more detail.
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
January 8, 2016 at 10:52 am
Sl33py (1/8/2016)
I run the job which is a basic select * but it produces varying results each time.
There are a few possibilities.
1) Changes in the WHERE or JOIN clauses
2) You're mistaking the changing order of the records as varying results when really all that is happening is you're getting the same records just listed in a different order because you have no ORDER BY (and this is okay and completely harmless).
3) Someone is messing with the table data and you can't get a consistent result because your data keeps changing.
January 8, 2016 at 11:17 am
null
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 8, 2016 at 11:18 am
Having the package property IsolationLevel set to 'ReadUncommitted' while selecting from a volatile table, or setting (NOLOCK) hint in the DataFlow source SELECT statement?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 8, 2016 at 11:21 am
Instead of making all kinds of assumptions, why not wait for the OP to answer what was already asked?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 11:32 am
Alvin Ramard (1/8/2016)
Instead of making all kinds of assumptions, why not wait for the OP to answer what was already asked?
Sl33py seems to have dozed off 😀
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
January 8, 2016 at 12:08 pm
Phil Parkin (1/8/2016)
Alvin Ramard (1/8/2016)
Instead of making all kinds of assumptions, why not wait for the OP to answer what was already asked?Sl33py seems to have dozed off 😀
Guess we can let him sleep. At least he didn't say his question was urgent. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 8, 2016 at 12:38 pm
Phil Parkin (1/8/2016)
Alvin Ramard (1/8/2016)
Instead of making all kinds of assumptions, why not wait for the OP to answer what was already asked?Sl33py seems to have dozed off 😀
Seems to me that if someone asks a question and doesn't come back within a day he's just being sensible instead of expecting to be our most important and urgent concern as so many do.
Tom
January 8, 2016 at 2:26 pm
Phil Parkin (1/8/2016)
Alvin Ramard (1/8/2016)
Instead of making all kinds of assumptions, why not wait for the OP to answer what was already asked?Sl33py seems to have dozed off 😀
That's OK; we're mostly just here to score talking points, promote our blog, or kill time while a database restore completes. :Whistling:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 9, 2016 at 9:55 am
Sorry All, Different Timezone here in SA.
We pull ALL the data for our daily summary tables over every day for a full financial year from Micros, this goes into our staging table and so far the row counts are matching up.
The issue comes when we perform a task to pull data from the staging to the destination, we use an OLE DB source and destination with a selection of all columns with no where clauses.
We run it once it gives lets say 275000 out of the 350000 in the staging. we truncate and run again and it then produces the correct numbers.
Not sure where to start. any clues as to where I could start looking would be GREAT.
January 11, 2016 at 4:20 am
Sl33py (1/9/2016)
Sorry All, Different Timezone here in SA.We pull ALL the data for our daily summary tables over every day for a full financial year from Micros, this goes into our staging table and so far the row counts are matching up.
The issue comes when we perform a task to pull data from the staging to the destination, we use an OLE DB source and destination with a selection of all columns with no where clauses.
We run it once it gives lets say 275000 out of the 350000 in the staging. we truncate and run again and it then produces the correct numbers.
Not sure where to start. any clues as to where I could start looking would be GREAT.
1) Are you sure there are no other processes that touching that staging table? Something that may cause locks or cause some of the records to not show as committed? Perhaps the initial pull is happening while the staging table is still loading?
2) What version of SSIS are you currently using? What version of SSIS was the package created as (I.E., is this an upgraded package from a previous version of SSIS, because that makes a difference).
3) Is this happening every day at the same time of day, or can you run the package at any time in the day and get the same "shortened" record set during the first run and the full records during the second run?
4) What do the logs say? Any errors? If you're not logging the package, try running it in BIDS / VS and copying the output to a text file so you can search for errors or the task name and post the details of that execution / error here.
5) Is this only happening in Production or is it happening in your non-production environments too?
January 11, 2016 at 5:46 am
Sl33py (1/9/2016)
Sorry All, Different Timezone here in SA.We pull ALL the data for our daily summary tables over every day for a full financial year from Micros, this goes into our staging table and so far the row counts are matching up.
The issue comes when we perform a task to pull data from the staging to the destination, we use an OLE DB source and destination with a selection of all columns with no where clauses.
We run it once it gives lets say 275000 out of the 350000 in the staging. we truncate and run again and it then produces the correct numbers.
Not sure where to start. any clues as to where I could start looking would be GREAT.
Do you do any processing of the staging table data between Micros and your destination table? UPDATES, Derived Columns etc.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply