Thursday, April 19, 2012

SSIS - RowNumber() Partioned By

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!

 Then add a script component and configure it as follows...First let's grab out input columns.

second let us add our Output row, I called it here PORT

Now let's configure out script using the language

Of course you could get fancy, add a try and catch, give meaningful names to your variables and such...

Our output:

Hope that helps someone....

Thursday, April 12, 2012

SQL Server 2012 RTM Cumulative Update 1

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

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 :)

Wednesday, April 11, 2012

SSRS get lat's sunday's date expression!

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...

Tuesday, April 10, 2012

T-SQL Tuesday #029 –SQL Server 2012 useful feature!
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 a immunization file that we get from the depart of health, that has patients vaccination records that could span up to 600 columns and I had to do it in a script task, create each output column with it's data type manually!!!

So if you had a file like this.....

You would end up with that in 2008!

But with 2012 you get that :)

Now we're talking! it looks like my data file!!
Thanks again Microsoft for fixing and improving the Flat File Source....

Sunday, April 8, 2012

71-467 Review, Design Business Intelligence Solutions w/MS SQL Server 2012 - 071-467

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

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.

Each case is a long business scenario about the current business' environment and what's the business requirements, technical requirements and an exhibit of the current business (database table, mart tables or servers layout).

Each case had 7 to 8 questions, you can't go back to the case once you finish answering and reviewing its questions.

The whole exam was 53 questions, after the 3 cases I got 31 questions, mix of the normal known format of the 4 to 5 multiple choices questions that I'm used to, and a new interactive format, these questions had some properties layout and you pull the right relationship or property for an item, just like a drag and drop.

Also some questions asks you on how to do such a thing and you'll get 6 or 7 steps and you need to select whatever applies in the right correct order, note that you don't tell you how many steps you could use !!

Also some questions had 5 to 6 multiple choices and ask you to select all that applies and you could select all of them!! not like before which tells you select 2 or 3 choices!

To recap, my advise to you, don't come near that exam until you really have that wide knowledge of all these technologies and have even some experience in it, it wasn't the easy or pleasant as I thought, I'm already working in BI since 2008 and have MCITP BI 2008! But I'm happy that I had this experience to know what to expect next time I go for the exam.

Well, the good news is that I didn't get the "FAILED" status yet as it's a beta exam and the results will be e-mailed once the live exam is live and available for booking.

practice, study, practice and then book the exam :), hope that helps someone...

Tuesday, April 3, 2012

SSIS No Column Names in OLEDB Data Source

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

Just add:


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.