October 29, 2010 at 3:53 pm
Hi,
I have a SSIS package that should truncate my staging table, reseed the identity value to 100 and then insert some data into the staging table.
The problem is that although the package executes correctly, the reseeding doesn't work and the data that is inserted continues from the original identity value (i.e. it just keeps incrementing).
However, if I execute the 'Execute SQL task' and then insert a row of data manually into the staging table in SSMS it works and the row gets an identity value of 100. If I then run the Data Flow Task the values are inserted but the identity value continues from the original identity value (i.e. from about 20102)
What am I doing wrong?
My package contains the following:
Execute SQL Task connected to my PracticeDB database (this is my staging table):
TRUNCATE TABLE PersonName;
DBCC CHECKIDENT(PersonName, reseed, 100);
Data Flow Task - simply take data from one table and insert into another table:
'OLE DB Source' with SQL command from the AdventureWorks2008R2 database:
SELECT Person.Person.FirstName, Person.Person.LastName,
FROM Person.Person
This is linked to 'SQL Server Destination' connected to my PracticeDB database table PersonName
Weirdly, it did work twice - the only change I made was to add 'GO' to the Execute SQL Task, which failed, I then removed it, ran it again and the reseed worked - twice. I then ran it again (no changes) and it doesn't work again.
Any help greatly appreciated! 🙂
October 29, 2010 at 3:58 pm
I believe this is because SSIS won't refresh metadata at runtime. This part confuses me:
Weirdly, it did work twice - the only change I made was to add 'GO' to the Execute SQL Task, which failed, I then removed it, ran it again and the reseed worked - twice. I then ran it again (no changes) and it doesn't work again.
The only conclusion I can come to here is the error caused a meta-data refresh, briefly. Why it didn't keep it is beyond me if it did, though. Did you perhaps go into the data flow object between steps and 'check' on things? That can refresh the meta-data during design.
I've had to suddenly learn a lot on the topic of non-dynamic metadata and I'm not sure I have all the moving parts right, but that's where I believe your answer lies.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 4:02 pm
I don't agree with your thinking Craig because the identity is not metadata that is being held by SSIS. It resides in the database engine; well, that's where it should be.
It looks almost line the reseed is not being committed, but that doesn't make sense to me.
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]
October 29, 2010 at 4:08 pm
Alvin Ramard (10/29/2010)
I don't agree with your thinking Craig because the identity is not metadata that is being held by SSIS. It's resides in the database engine; well, that's where it should be.It looks almost line the reseed is not being committed, but that doesn't make sense to me.
I had thought so too, but some reading I did (warning: untested rumors at best) pointed out a few odd things that might sneak into the metadata. Hopefully next week I'll get the time to build out the full version of "That's being stored... WHERE?!"
I'd be happy to be wrong on this one, but hoped I'd least give him a line of exploration until someone who knows for sure can show up and prove me wrong. :hehe: Well, or until I can get a chance to go deploy a test package on a server and find out for sure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 4:12 pm
Hi,
Thanks for the super-fast replies!
I have just copied and pasted the parts of the package into a new package, reselected the data connections and it worked - 3 times in a row - no changes all I have done after each run is to run a select * from personname. I then restarted BIDS to see if it still worked and it did once more. BUT now it doesn't work again.
I also thought that it was something to do with a commit not completing, but I can't understand why it works sometimes.
Could it be to do with my environment (no laughing though!) - I'm using a MacBook with Parallels running a VM of Windows 7, on here I'm running SQL Server 2008 R2. The VM has 1.5GB memory, but it can be a bit slow (more memory ordered!)
I suppose my question should also be - am I approaching this requirement in the right way?
Thanks
October 29, 2010 at 4:14 pm
Oh, and 'her' not 'him' 😀
October 29, 2010 at 4:28 pm
HelsUK (10/29/2010)
Hi,I have a SSIS package that should truncate my staging table, reseed the identity value to 100 and then insert some data into the staging table.
The problem is that although the package executes correctly, the reseeding doesn't work and the data that is inserted continues from the original identity value (i.e. it just keeps incrementing).
However, if I execute the 'Execute SQL task' and then insert a row of data manually into the staging table in SSMS it works and the row gets an identity value of 100. If I then run the Data Flow Task the values are inserted but the identity value continues from the original identity value (i.e. from about 20102)
Sorry, m'lady.
I just realized something in what you said. Can you confirm that your TRUNCATE TABLE is also always working? The fact that it's restarting at 20102, and not 1 OR 100, confuses me, unless the table was originally seeded at 20102 and that's where the truncate resets it to?
And, just offhandedly since you asked about the approach, why are you reseeding in the first place?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 4:52 pm
No problem kind Sir!
Yes, the TRUNCATE is always working - there are always 19972 rows in my table after running the package. The identity was continuing from 19973 (not 20102 sorry). Interestingly the reseed is also working because if I stop my package after the reseed has completed but before inserting the data and manually INSERT a row of data it does give an identity value of 100. If I then continue the package so that it does its insert, those rows continue from 19973. So the database knows the reseed has occurred, but SSIS doesn't (same thing after a BIDS restart too).
I'm reseeding because this is a staging table that will be loaded hourly with source data of many thousands of rows which will then be loaded into a data warehouse. The constant truncate and populate will eventually max out the identity value so I'm reseeding to stop this happening. The purpose of the identity column is to use it for table joins during the ETL processing for performance (source system keys can have issues and are not integers).
To make things worse, I thought I'd just run through some tests to see if I could figure out how it could 'just work' sometimes and it is now working consistently (I've not made any changes, just exited and restarted BIDS - which I had done before). It has now worked about 20 times in a row and now I don't seem to be able to break it. Obviously I am happy it works but it worries me that it could just stop in future and as a logical person things that 'just work' usually have a logical reason for doing so!
October 29, 2010 at 5:03 pm
HelsUK (10/29/2010)
Yes, the TRUNCATE is always working - there are always 19972 rows in my table after running the package. The identity was continuing from 19973 (not 20102 sorry). Interestingly the reseed is also working because if I stop my package after the reseed has completed but before inserting the data and manually INSERT a row of data it does give an identity value of 100. If I then continue the package so that it does its insert, those rows continue from 19973. So the database knows the reseed has occurred, but SSIS doesn't (same thing after a BIDS restart too).
Good to know...
I'm reseeding because this is a staging table that will be loaded hourly with source data of many thousands of rows which will then be loaded into a data warehouse. The constant truncate and populate will eventually max out the identity value so I'm reseeding to stop this happening. The purpose of the identity column is to use it for table joins during the ETL processing for performance (source system keys can have issues and are not integers).
TRUNCATE TABLE is supposed to reset your seed to the original value. This is an unnecessary step, just so you know. I'm not sure why you're getting different results.
To make things worse, I thought I'd just run through some tests to see if I could figure out how it could 'just work' sometimes and it is now working consistently (I've not made any changes, just exited and restarted BIDS - which I had done before). It has now worked about 20 times in a row and now I don't seem to be able to break it. Obviously I am happy it works but it worries me that it could just stop in future and as a logical person things that 'just work' usually have a logical reason for doing so!
That's one I'll have to research and sleep on. I'm happy it's working, but it shouldn't be necessary in the first place for your intentions. Best of luck, I'll check in tomorrow if I can. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 29, 2010 at 5:13 pm
Ah bingo - I didn't realise the TRUNCATE would reset the seed - this is perfect and exactly what I need to happen. The reseed is now a redundant step.
I amended my package and it does consistently start from 1 (the identity seed in the CREATE TABLE statement).
Although I'm still intrigued why I didn't get consistent results, I can live with it as it isn't something I should ever need to do (I can't currently think of a scenario when I'd want to reseed without a truncate).
Thanks so much for your help - you've been a star! 😀
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply