Blog Page

SSIS Transformation Documentation

sqlsafety 0 Comments

I have been asked several time to provide a quick and easy outline of the different SSIS transformations.  The typical questions are:

What does the task do?  What are the configuration options?  What are the advanced configuration options?  How many inputs?  How many outputs?

I began compiling this sometime ago and it is still a work in progress and reflects online documentation and links from MSDN and Technet.  You can download it here and I will continue to update and post the most recent copy at this link.

Please feel free to share and give me input and requests.

Database Administration Questions

sqlsafety 3 Comments

Here are the questions that I ran out of time to answer.  Sorry for the delay!!

1. Max files to use detach and reattach
    ANSWER sp_attach_db only supports up to 16 files while CREATE DATABASE ATTACH supports more

3. Automate restore using single file identify the fileid in the restore

    I am still working on this.  I would recommend log shipping in such a case as it is automated and rather straight forward to configure.  Technet outlines the configuration here.

4. CWL High Availability
   NO MOC or CWL courses for High availability
   SQL 2005 has high availability ONLY MOC 2788AC

5. PowerShell Remoting to SQL
   Data access using powershell is possible
   Pass query specifying connection
http://www.systemcentercentral.com/powershell-how-to-connect-to-a-remote-sql-database-and-retrieve-a-data-set/
    Remote PS in SQL
http://www.mssqltips.com/sqlservertip/2516/enable-powershell-remoting-on-sql-server-instances/

6. Create jobs, NOT schedule them

     I will have to follow up on this

7. FILESTREAM examples
   Sample script can be downloaded here.

I will post a follow up on the two remaining issues within a week.

#SQLSatNash #SQLSaturday I’m Going to Nashville!!

sqlsafety 0 Comments

SQL Saturday Nashville is quickly approaching and here are 10 reasons that you should attend.

1. Wendy Pastrick, @wendy_dance, Read Dirty to Me – Isolation Levels/User Impact  Gotta be honest…  The session title is what sold me!!  LOVE IT!

2. Allen White, @SQLRunr, Automate Your ETL Infrastructure with SSIS and PowerShell Obviously I am a bit biased when it comes to SSIS, but SSIS AND PowerShell!!!  AWRIGHT!!

3. Andy Leonard, @AndyLeonard, “I See a Control Flow Tab. Now What?” Anyone who works with SSIS should attend any session that Andy presents on Integration Services!!

4. Carlos Chacon, @CarlosLChacon y @TicoDba, Judge, Jury, and Executioner–A First Look At Execution Plans.  Just like number 1 the session title immediately spoke to me.  Be for real man.  I was a cop for 24 years NO WAY I’m missing this one!

5. Denny Cherry, @mrdenny, SANLess Clustering on the Ground and In the Cloud Need to create a cluster where traditional shared storage clusters are impractical or impossible?  This is the session for you.

6. David Klee, @kleegeek, SQL Server Virtualization 101 Need to know tips and tricks for virtualization as a dba? Just show up.

7. Russ Loski, @sqlmovers, SSRS data driven subscription using SSIS Want to provide data driven subscriptions for your reports, but don’t have SSRS?  Your answer awaits here.

8. Shehap El-Nagar, @MSQLPerformance, Data Warehousing Guidelines for BI and BAM solutions Need to get up to speed on definitions and terminologies with data warehousing and business activity monitoring.  Stop by and listen in.

9. Daniel Evans, @danthebiman, Self-Service BI with SSAS Multi-Dim How can your multi-dimensional data models present visual analysis using Power View, SharePoint, and Excel?  I’m not going to ruin the ending you have to attend to see.

10. Aaron Nelson, @SQLvariant , SQL Server Database Development with SSDT  How can you do database development in BIDS…  I mean Visual Studio…  I meant SSDT. 

#SQLSat233 Washington DC Count Down #SQLSat

sqlsafety 4 Comments

We are just days away form SQL Saturday 233 in Washington DC and if you are on the fence as to whether you will be attending or not let me give you 10 reason why this is a must event.

1. Andy Leonard, @AndyLeonard, Using BIML as an SSIS Design Patterns Engine.  For almost a year now I have attempted to see Andy present this session and have come up dry.  I am psyched to FINALLY be able to attend this introduction to BIML!!

2. Brian Kelley, @kbriankelley, Being the Swiss Army Knife of DB Pros.  I love the synopsis of this session and am excited to see what Brian has to present on being a SQL Server “generalist”.

3. Jason Brimhall, @sqlrnnr, SQL 2012 Extended Events. Extended Events have been available since SQL 2008, but I have yet to really work with them.  I am hoping to get some tips from Jason and gain some more insight into the intricacies of Extended Events, besides in SQL 2012 they can be configured in the SSMS GUI.  C’mon man.  Don’t judge me cause I’ve become lazy.

4. Steve Jones, @way0utwest,   Branding Yourself for a Dream Job.  This session may be the most important for me for this event.  That’s right!!  It is very hard for potential employers to get past the whole retire police officer thing with me.  I am hoping to get past the whole, “you want a donut?” thing!!  You would think my breath taking good looks would be enough huh?

5. Jessica Moss, @jessicammoss, Data Warehouse ETL Rollback with SSIS 2012.  For the past year I have worked a great deal with SSIS and I am looking forward to this session to get some tips on removing data that was loaded incorrectly using SSIS.

6. Grant Fritchey, @GFritchey, Tune Queries By Fixing Bad Parameter Sniffing.  Having just worked on a project with a particularly burdensome query due to parameter sniffing this session should prove to be quite insightful.

7. Andrew Kelly, @GunneyK, Policy-Based Management and Central Management Server.  Looking for a means of centralizing administration and control of you SQL environment? 

8. Kevin Goff SQL Server 2012 Columnstore index.  Columnstore indexes were introduced in SQL 2012 and can provide an incredible performance enhancement over traditional indexing strategies.  It’s time to see what you can gain from this new indexing strategy!

9. Wayne Sheffield, @DBAWayne, Crazy Things That Developers Do, Part I.  Having gone from administration to development this should prove to be a somewhat embarrassing session for me to attend.  I actually have some stories I could share about this, but until the statute of limitations expire all I can say is, “It wasn’t me.”

10. IT IS WASHINGTON DC!!  REALLY?!?! What more of a reason are you really looking for?  Our nations capitol…  A FREE all day event…  True quality presenters and sessions…  Networking and fellowship…  C’mon man!!  Just register and show up.

#SQLSat248 #SQLSat Presentation And THANKS!!!

sqlsafety 0 Comments

I want to thank everyone for attending my SQL Saturday session, Dealing With Errors in SQL Server 2012.  I really appreciate all of the feedback that I received and plan on tweaking this presentation for SQL Saturday Washington DC and SQL Saturday Ancona Italy based on your input.  As promised I have uploaded all presentation material for download here as well as on the SQL Saturday #248 schedule here.

Unfortunately/Fortunately this event was VERY close to my home, 2 hours driving distance.  It was nice not having to fly to a SQL Saturday, but it was a bit too close to my son’s baseball tournament just a couple hours drive away.  I want to thank Kathi Kellenberger for her AWESOME presentation on SSIS 2012 before mine and to apologize to Tim Mitchell for missing his presentation on Make Your SSIS Packages Run Faster: 15 Quick Tips

Based on several requests I will be posting a follow up post on data redirection and error description retrieval in the very near future.  I cannot thank all the volunteers and staff for this AWESOME event enough and I feel very fortunate to have attended and be involved.


Hit Counter provided by Sign Holders