The Dynamic Process of Loading Data
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jchan/2781.asp
-
Hi Janet, thank you for writing your experience using DTS to load the data into your stage tables and into your dimension tables. I had similar experience, using DTS to load the data into stage then into fact and dimension tables from different source systems: spreadsheet, AS/400, DB/2, MS Access and flat files. I did similar approach, i.e. using dynamic process, for the same reason (migration between Dev-QA-Prod environments) but instead of reading from SQL table, the dynamic package reads from a parameter/control file. I had the staging database name, names and locations of files to be imported etc in this parameter file. The reason of using a file is that we had different SQL Servers for Dev, QA and Prod env so if the dynamic process reads from a SQL table in SQL Dev for example, when the DTS packages are migrated to Prod env it can't access SQL Dev firewall constraint).
We had about 40 or so sources (tables or files) for fact & dimensions from about 5 or 6 different source systems. Instead of creating a DTS package for each of this 40 sources, I created 1 DTS package for each data source. For example: 2 packages for DB/2 (they have 2 different ERP systems runing on DB2), 1 package for the XLSes, 1 package for the MS Access DBs etc.
I also share the same situation as you about SOX, i.e. there is a lot of procedures and constraints in the production because of Sarbane Oxley, and because of best practices too
Thanks again for sharing your experience.
Kind regards,
Vincent
-
Hi Janet,
I think you have a lot to offer and would enjoy reading your articles more if I felt you were not using them to make a dig at someone or other (ie managers, production DBA's - from almost all your previous articles). We ALL make mistakes, but someties you have to let people do their job, even though mistakes may be made. There are often a lot of reasons for such mistakes happening, not least the issues of complexity, and of responsibility and accountability. If some juniors do not understand what I have done, then I believe it is for me to help them to understand (we were all juniors once and I for one am extremely grateful to those mentors that have helped me in gaining knowledge, and I'm still learning through people on sites such as this). I have worked on both sides of the fence - developer and DBA - and have worked in both small companies and large multinationals, so have experienced all the dynamics and politics that come with the roles I have held, and will agree that some places have been very challenging whilst others were an absolute pleasure to work for. I admire you for being prepared to put your technical skills and experience into articles for others to scrutinise (and maybe even critises). So, keep up the good work, but just leave out the digs that detract from the great stuff you are writing.
Ian Garrett
A Production DBA (and ex-developer), and proud of it!
-
Ian put it very well above. I too am distracted by the tone of the articles at times. The technical content seems good.
-
I agree with Chris and Ian. I enjoy reading your articles. The content seems great. I think it will be good if it is goal is kept to the purpose of sharing knwoledge.
-
It was a good read. There was plenty of information and I understand your want to explain why you took the route you did. Having said that, it does seem a little personal. I do not, however, get the impression that it was intentional so just keep it in mind as we all look forward to hearing more from you.
-mike
-
I'm impressed with your article, Janet. And I find Vincent Rainardi's experience very helpful. Being the novice that I am with SQL DTS (I shudder when I think of DTS), I'd be interested in seeing some examples so I'll have something to build upon. In the next two years I'm charged with migrating a mainframe database to the SQL Servers. YeeHAA!! What a challenge! So, examples would be helpful for me and some of my associates.
And, Janet, I hope that you'll take the encouragement of the other forum members in the kind manner they meant it. Many years ago, I heard someone speak these words and they became a foundation stone in my daily walk: "We never build ourselves up by tearing other people down." I heard it given again recently in a world-wide broadcast, and the speaker said, "It is a true principle." And I, too, know that it is true.
Thank you for your approach to "developing a long term solution". My sentiments exactly. Keep up the good work.
"Keep smiling ... it gives your face something happy to do ... and it makes people wonder what you're up to!"
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
-
I started converting alot of our DTS packages to SSIS. It occurred to me that I might be able to do the same sort of thing using SSIS. I would be interested in hearing from anybody who has does something similiar and might be able to provide examples and/or discuss the issues they encountered.
-
you can move packages from test to production using a script.
outlined here:
from http://www.dbazine.com/sql/sql-articles/larsen8
I had to tweak the code, but it works. We have over 330 packages to load our data warehouse. I use the code when moving production packages to test for end to end testing.
-
I think it's a great article, and it appears that it's only the guys that think you're making a dig at someone. Don't worry about it.
-
Thanks for the article Janet. I particularly like the "multi-threaded" approach with the temporary jobs. Even though I don't do any DTS work at the moment, I like the approach you have taken in finding a long term solution. Obviously giving it a bit of thought before jumping in and doing the work.
I didn't think that you were having a dig at anyone. It seemed more of a reminder to us all that we should recognise our own fallability. You even point out a flaw in your original design, which you then corrected. You could simply have left that flaw out and only given us the final solution, but I think you gave the article greater value by allowing us to see the path you took to get to your destination.
Again, well done.
-
Hi Norm Johnson, if you send your email address to irainardi@yahoo.co.uk I'll send you some samples of DTS packages that I used to extract from source systems to stage in some data warehousing projects.
Regards,
Vincent
-
"for example they may forget to change the connection property"
Ugh, this used to be such a pain in the keister but then someone turned our group on to using UDL's for our connection objects. Specify that they should always read their properties from the server and set a standard that they will be located in the same place and voila, all is done.
We've also done some stuff with dynamically setting file paths based on environmental variables which has also been a boon for changing between dev & production.
-
I agree with some of the others... the tone of the article is a bit distracting and takes away from the good technical content.
It would also be nice if you posted some of the code you used. Active X script to change the file name, a proc, etc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I also agree about some of the points made on distracting remarks, having gone through previous articles Janet has written. Technical content is excellent, though, Janet.
Don't think Jamima's comments on a male thing was very helpful, or statistically sound (being as there are 400,000 members and only a few comments).
Don't be disheartened Janet, N Johnson put it very well about Ian's comments and what I guess he was trying to say.
Thanks
Liz
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply