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 05, 2010 9:04 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 788, Visits: 1,914
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 05, 2010 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
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 05, 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 @ 2:26 AM
Points: 41,496, Visits: 34,417
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 06, 2010 5:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 12,174, Visits: 9,120
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 06, 2010 8:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #878161
Posted Sunday, March 07, 2010 7:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 788, Visits: 1,914
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 09, 2010 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
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 09, 2010 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 12,174, Visits: 9,120
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 09, 2010 1:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
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 09, 2010 1:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 04, 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