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


SSIS Interview Questions -COMPLEX 1ne


SSIS Interview Questions -COMPLEX 1ne

Author
Message
RonKyle
RonKyle
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

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



Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23888 Visits: 5314
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223205 Visits: 46294
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, MVP, M.Sc (Comp Sci)
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


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62159 Visits: 13297
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 :-D

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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66341 Visits: 18570
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

RonKyle
RonKyle
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

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



sql_learner29
sql_learner29
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1849 Visits: 938
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

Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62159 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sql_learner29
sql_learner29
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1849 Visits: 938
Hi
Thanks ...but is there any way to hide your passwords when you deploy or move your packages from one environment to other..??????


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

Thanks

SanDiegoBeach
SanDiegoBeach
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 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.
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