Another BI & Programming Blog - Jason Yousef

Today I saw an interesting question in EE about how to create a RowNumber in the data flow task but partitioned by an ID column. Row Number is easy and could be done using a script task in the control flow or inside a script component in the data flow task, there is also a Row Number transformation, a free community extension. But the partition by is the problem here, which just need a custom script to do it, and the answer to that is by using a synchronous script component as follows: Our Input: Add your flat file source, I'm not going to go into details about the flat file source, you could use any source and configure it the way you want, the point here is our input is 2 columns and we need to create the row number column partitioned by the ID column. I'm assuming that the input is sorted on the ID< if not, so you need to add a sort transformation to the flow before the script component. and thanks to Valentino ( B | T ) for reminding me!  The...

Microsoft has release the SQL Server 2012 RTM Cumulative Update 1, the Update contains 53 hotfixes for SQL server 2012. You may download it from http://support.microsoft.com/kb/2679368/en-us The update needs to be requested and delivered to your e-mail inbox, it comes in 3 self-extracted zip archived files. 1- Data-Tier App Framework (446570) 2.9 MB 2- PowerPivot for Excel ( 446571) 122 MB 3- SQLServer2012-KB2679368-x64 ( 446572) 475 MB Please remember to test before deploying to your production servers :)

A recent question about how to get the last Sunday's date expression in SSRS. so it's simply could be done using the SWITCH function in SSRS, the same concept could be used to get any date of the week's days. =switch ( Weekday(today) = 1, today, Weekday(today) = 2, dateadd("d", -1, today), Weekday(today) = 3, dateadd("d", -2, today), Weekday(today) = 4, dateadd("d", -3, today), Weekday(today) = 5, dateadd("d", -4, today), Weekday(today) = 6, dateadd("d", -5, today), Weekday(today) = 7, dateadd("d", -6, today) ) Hope that helps someone...

Well, this is my first time to participate in the T-SQL Tuesday, and I'm honored to talk about a new SQL server 2012 feature, and I hope I can do it more often.  SQL 2012 is really a major release and rich full of features, but as an ETL developer I'll pick SSIS as my candidate to demo and share my happiness about one new feature. It really saved me headache and made me say 'Thanks Microsoft' when I heard that it's finally here!  it might sounds simple but it was really a big deal in SSIS. The feature is the new and improved Flat File Source , it now can support ragged uneven number of columns!! yes! finally they thought about it..... Before if you had a flat file that has uneven column numbers, you're out of luck!! you need to use a script task, or read it as one line and do some monkey work or even use bulk insert with the annoying format file to skip a column!! Or use a community task such the " Delimited File Source " oh my god! I remember...

I was one of the first fortunate people to take the 071-467 beta exam (Design Business Intelligence Solutions w/MS SQL Server 2012), using the free SQL 2012 Beta vouchers. Check this link to see what's covered in the exam and get idea of what I'll be talking about http://www.microsoft.com/learning/en/us/exam.aspx?id=70-467 As the name of the exam states, Design Business Intelligence Solutions w/MS SQL Server 2012, it was mostly about every business intelligence product that Microsoft has, you need to be familiar with a variety of tools, starting down from Excel SSRS, SSIS, SharePoint, PerformancePoint and up to SSAS (Analysis Services) BISM and Tubular modes. I must admit that exam was hard and I didn't had the wide knowledge for all these tools and not even much experience about SSAS tubular mode.  It uses a totally different approach than any other SQL server exam that I've taken in the past, it consists of 3 separate cases and 31 multiple choices questions. ...

Have you had this problem before with a stored procedure that has a temporary table? While I was using a stored procedure as a source for my OLEDB connection, No columns was listed in the source editor. After some research, I landed at http://msdn.microsoft.com/en-us/library/ms173839%28v=sql.105%29.aspx Just add: SET FMTONLY OFF exec STOREDPROCEDURE This will force the parser to return only the metadata to SSIS. Hope that helps and feel free to share any findings on when do you get this issue.