Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The Dynamic Process of Loading Data Expand / Collapse
Author
Message
Posted Wednesday, December 27, 2006 11:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:03 PM
Points: 2,839, Visits: 3,146
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jchan/2781.asp
Post #333025
Posted Tuesday, January 23, 2007 3:11 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:56 AM
Points: 81, Visits: 190

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

Post #338895
Posted Tuesday, January 23, 2007 5:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 9:22 AM
Points: 216, Visits: 1,372

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!

Post #338924
Posted Tuesday, January 23, 2007 7:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 10:48 AM
Points: 966, Visits: 933
Ian put it very well above. I too am distracted by the tone of the articles at times. The technical content seems good.


Post #338967
Posted Tuesday, January 23, 2007 7:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:36 PM
Points: 348, Visits: 24

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.

 

Post #338969
Posted Tuesday, January 23, 2007 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 12:03 PM
Points: 28, 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
Post #338971
Posted Tuesday, January 23, 2007 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:47 AM
Points: 21, 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!"
   
Post #338986
Posted Tuesday, January 23, 2007 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 8:00 AM
Points: 10, 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.  


Post #339008
Posted Tuesday, January 23, 2007 9:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:40 PM
Points: 55, Visits: 239

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.

Post #339056
Posted Tuesday, January 23, 2007 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 20, 2012 10:44 AM
Points: 1, 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.
Post #339103
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse