SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Dynamic Process of Loading Data


The Dynamic Process of Loading Data

Author
Message
Loner
Loner
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7528 Visits: 3364
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jchan/2781.asp
VincentRainardi
VincentRainardi
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 191

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


humbleDBA
humbleDBA
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1116 Visits: 1508

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!


ChrisMoix-87856
ChrisMoix-87856
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2550 Visits: 941
Ian put it very well above. I too am distracted by the tone of the articles at times. The technical content seems good.



TJ-183067
TJ-183067
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 29

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.


Mike Dillon
Mike Dillon
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 38
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
N.Johnson
N.Johnson
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 43

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!"

Steve Schwarting
Steve Schwarting
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 20
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.



SQLGuy64
SQLGuy64
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 295

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.


Jamima Coder
Jamima Coder
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 3
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search