Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1266 Visits: 3341
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47273 Visits: 44392
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21089 Visits: 18259
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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



NoraG
NoraG
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 916
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
NoraG
NoraG
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 916
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 Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

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