Another BI & Programming Blog - Jason Yousef

Hi, I had seen a question asking about getting all the excel sheet names in a work book, so I've created the attached example package. Basically I'll loop over a specific folder, get all the excel file names into a variable, pass it to a script task to get me the worksheet names.   In the SCRIPT TASK, add a reference to  MICROSOFT.OFFICE.INTEROP.EXCEL Then add this code block.. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase     Enum ScriptResults         Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success         Fail...

I've a column type "datetime" that reads 09/23/2011 12:00:00 AM, need to show only the month and day portion seprated by "/" or "-". There're 2 ways to do it in SSRS: 1-the easiest way :) and the right one too... =Month(Fields!Tuesday.Value) & "/" & Day(Fields!Tuesday.Value) 2-the long fiasco way ! I was just fooling around !! =StrReverse(Replace(StrReverse(left(Fields!Sunday.Value).ToShortDateString,5)),"/","",1,1)) It's your call...

I'm so excited to hear about the new VS 2011, i've downloaded and played around to create new databases and do some stuff in denali and it has the new JUNEAU featues... Give it a shot.... HERE

Ever got this message and wondered what does it means? The query works fine in SSMS but fails to refresh and update the column names in SSRS !! It happen to be that I've used the same column names in my query, but SQL handled it as I was using a table prefix in my join. Just alias your column names !!!  and remember not to select * !!!

I was upgrading SQL server 2008 to 2008 R2, the installtion stops with error "SQL Server 2005 Express Tools in installed"  !! please uninstall, but unfortunately I don't have that!! and can't find it under the list of installed programs in the control panel! Tried the skip check flag "/skiprules=Sql2005SsmsExpressFacet"  with no use !! I took a backup of the whole registry, and removed the whole "Registry Key 90" folder! HKEY_LOCAL_MACHINE > Software > Microsoft > Microsoft SQL Server > 90 It worked!!

=WeekdayName(datepart("w", Fields!packagestart.Value))

I have an Excel file that I want to import into SQL Server. The package works fine! Except it nulls out some columns. and since these columns are null, it's picking null for the rows. interesting, you could change the number of rows that's being used for guessing by the jet engine... http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/54dc94c5-280b-41e5-82fe-26925f9eb95b/ I never had this problem before, so I'm learning now!! Learned that a TypeGuessRows in registry which is 8 by default, and this is number of rows which Jet engine try to fetch "data type" can be changed up to 16. At the following location. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel Another property named IMEX which is for Mixed types in excel. actually when Jet engine mark any column as mixed type ( like columns which character and numeric together ), then IMEX=1 excel will always fetch data as text. so use IMEX=1 and set TypeGuessRows as...