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 ««12345»»»

SSIS Interview Questions -COMPLEX 1ne Expand / Collapse
Author
Message
Posted Friday, March 5, 2010 9:04 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: Yesterday @ 6:35 AM
Points: 817, Visits: 2,060
never use the build-in slowly changing dimensions wizard

Why would you never use it? I'm not a big fan of most wizards, but I find this one very effective.



Post #877720
Posted Friday, March 5, 2010 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
RonKyle (3/5/2010)
never use the build-in slowly changing dimensions wizard

Why would you never use it? I'm not a big fan of most wizards, but I find this one very effective.
I have found this component helpful in the past as well, there is a Kimbal Method SCD component on codeplex that I would seriously look at, it is at:
http://kimballscd.codeplex.com/

I have not actually used it myself.

CEWII
Post #877731
Posted Friday, March 5, 2010 9:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 40,172, Visits: 36,563
da-zero (3/5/2010)

- never use the build-in slowly changing dimensions wizard


If you make such a statement in an interview, any competent interviewer will immediately follow up with 'Why?'



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #877735
Posted Saturday, March 6, 2010 5:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 13,245, Visits: 11,022
GilaMonster (3/5/2010)
da-zero (3/5/2010)

- never use the build-in slowly changing dimensions wizard


If you make such a statement in an interview, any competent interviewer will immediately follow up with 'Why?'


Well, thanks for interviewing me then

The MS build-in SCD component is for example not that efficient. It uses the OLE DB Command to perform the updates, which means that if a million rows have to be updated, a million different updates will be issued against the DB, instead of one big update. I'm not a DBA, but I think that is not so good for performance, locking and transactions.

The Kimball SCD at Codeplex however has much better efficiency, so this one is prefered above the build-in one.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #878139
Posted Saturday, March 6, 2010 8:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
cRuchika (3/4/2010)
and we avoid using AGGREGATE and SORT transformation...
.


I would be careful on the sort transformation. Many times a sort transformation is necessary when using a merge join due to the IsSorted property not properly functioning.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #878161
Posted Sunday, March 7, 2010 7:17 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 6:35 AM
Points: 817, Visits: 2,060
The MS build-in SCD component is for example not that efficient. It uses the OLE DB Command to perform the updates, which means that if a million rows have to be updated, a million different updates will be issued against the DB, instead of one big update. I'm not a DBA, but I think that is not so good for performance, locking and transactions.

This is true, but the SCD component should only be used for dimensions, and while dimensions can have a million rows, most don't, and even those that do won't need to updated to that extent on a given download. I would say use it except for the unlikely circumstance you describe above.



Post #878394
Posted Tuesday, March 9, 2010 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Thank u all for your valuable inputs....
Lets jump to another question:

How did you deploy your packages?
In this queston what is suitable as we dont deploy and use manufest ever we put in source safe and from there it goes to TEST/prod environment...

Can someone tell me about deployment checklist... what is it and what does it contain??????

Do we ever deploy or not???
Thanks



---------------------------------------------------

Thanks
Post #879353
Posted Tuesday, March 9, 2010 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:28 AM
Points: 13,245, Visits: 11,022
At my current project I deploy packages I'm developing in a subversion folder to the test environment by simply using the deploying capabilities of BIDS Helper in BIDS.

From test to production we have a script that simply copies the packages from one location (this can be the subversion folder or another) to the MSDB folder in the production SQL Server.

The script constains statements like this:

dtutil /FILE "\\someFolder\Release_x_a_b\SSIS\myPackage.dtsx" /DestServer /TargetServer /COPY SQL;"myFolder\myPackage" /Q

I think there are plenty of ways of deploying, but I'm only familiar with this one.

Regards.






How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #879388
Posted Tuesday, March 9, 2010 1:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
Hi
Thanks ...but is there any way to hide your passwords when you deploy or move your packages from one environment to other..??????



---------------------------------------------------

Thanks
Post #879662
Posted Tuesday, March 9, 2010 1:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 4, 2012 10:39 AM
Points: 96, Visits: 153
#11 I would disagree with your answer. I don't know at all how Checkpoints can considered expensive. All they are is a simple XML file that the package uses to record its progress and current values of variables. They only exist while a package is running and if a package fails. If the package succeeds, they are totally deleted. For a complex load package that has a long run time they can be a tremendous time saver.

For example, I have a very complex set of parent-chid packages that i use to load 100's of millions of rows of data. My SSIS packages do everything, from FTP, to unzipping several layers of compressed folders, to converting files from Unix to Windows, to loading and processing. If I get a bad file from a vendor, my package will fail (by design). When i resolve the file issue, I am able to restart my package using a checkpoint. I don't have to worry about restarting a 30 hour process from the beginning, or trying to figure out what was done and what wasn't. You are able to essentially nest checkpoints by using them both in parent and child packages.
Post #879698
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse