Monday, December 3, 2012

SSIS - Split records and export to flat files

Today's issue that we've a source database Sybase ASE, and old version is being used as a replica, I'm trying to extract some records, it's almost 750K to 1 Mil records and it needs to be exported to flat files and sent somewhere for further processing.

The files has to be no more than 10K records a file and it needs to get a specific naming convention...

I've done it differently than what I'm showing in the demo here... and I'll explain why...

the main component in the package is the "For Loop Container", then inside the container I'll have a "Data Flow Task".

I'll create 4 variables as shown below..

Add a "For Loop Container", configure as attached.. to use the variables and set the limit to 100K here

 Basically the first task, is a "Script Task", I'm only using it to debug my variables in a message box, enable or disable to debug, you could use "BreakPoints" which I've used in my original package and here only it's for demo.

 The way I'm debugging is so easy, just to display a message with the current variables' value to check if I'm heading in the right way!  again use breakpoints, add a watch and check your variables values...

 The data flow task...

I'm using a data generator task, to create my input can use your own source tables of course, I was reading from a sybase ASE database, an old version which doesn't support the Row_Number() function, so I couldn't build a column for the row numbers, and yes...I thought about a SQL server staging table...yes but it wasn't an option !

 I'm generating 520,000! yes weired number but I want to show the rest of the 20K records will get into a file of their own!


Just a row count of all the rows , please note that it'll run 6 times and it's not efficient...
You could use an "Execute sql task" to get you count(*) in the "Control Flow" and that will run 1 time, but here I'm using the data generator task....

Here it's my optional "Script component" to create a Row Number column, again I'm using that because I'm using the data generator task..or if you're reading from a flat file directly or if Row_number is not an option and no staging SQL table either !

 Conditional split to filter which rows where exactly need to write to our destination...

And here I'm setting the flat file destination..



I've an expression over the connection string to get me the file name dynamically to reflect which records in the file.


The last step here is a script task to update the variables' values...I'm not really sure why I've it in the control flow, but you can do it in the data flow task !
That's it! the folder when it's empty...
The folder when it got the files !!
Wrap up... again.. you don't need to debug your variables
use the data generator task...
use the row number task
or even the split task ..Yes !!  if you're reading from your source table and not from a data generator task, you might build your query to use the 2 variables and get only rows where the RN between these 2 variables... I didn't illustrate that here for simplicity.
it'd look like that...
 And yes I've an index on the RN column ...

The last screen shot here is not meant for people with weak hearts !! it's scary!!

Hope I've explained it clearly... and hit me up if you have a better way of doing it...

Wednesday, November 21, 2012

NJSQL Presentation - Let's talk dynamic

Let's talk dynamic - SSIS variables and expressions

So yesterday I was honrored to speak before the NJSQL group about "SSIS variables and expressions" I do apologize for my lengthy presentation as I went so deep into small details... but I was just trying to go with the flow, and naswering the questions...

I've attached a RAR file,  containing the slide deck  along with the demo scripts.  I hope you get as much out of SQL Server as I have, and Thanks for coming!

The slides can downloaded from

Friday, November 16, 2012

SSRS - Subscription comments location.

Today I was facing a client's problem in SSRS to edit the comments text for over 120 reports to point to a server name (Host Name) instead of IP, to begin with, it was a bad idea and judgment from the person who created the report. and secondly it's rarely to see a server that gets an IP change too!

But the two issues could happen and it's ok, let's face it and fix it.

Here's a screen shot, If you're not familiar with the subscription comments.

To locate it, just navigate to SQL server management studio (SSMS), and browse the :

[ReportServer].[dbo].[Subscriptions] Table

the column that has the comments is [ExtensionSettings]

just replace the text using your T-SQL query and you're done! the data type of that column in nText, so it won't with T-SQL replace function.

so you might use...

SET     S.ExtensionSettings =
FROM [ReportServer].[dbo].[Subscriptions] AS
SELECT [SubscriptionID], REPLACE(CONVERT(VARCHAR(MAX),[ExtensionSettings]),'','HostNameServer')  
FROM [ReportServer].[dbo].[Subscriptions]) AS
ON S.SubscriptionID  = T.[SubscriptionID]


Hope that helps someone...

Code has been formatted using...

Wednesday, October 31, 2012

MSDTC on server 'Server' is unavailable.

Just a quick post on an error that I got today...
Msg 8501, Level 16, State 3, Line 3
MSDTC on server 'ODCLG29' is unavailable.
That means the "Distributed Transaction Coordinator" service is not running.
ways to get to it...
Type "services.msc" in the run or search command.
or go to Control Panel --> administrative tools ---> Services
look for Distributed Transaction Coordinator, you may double click to start it or right click and select start.
You may further read about it here...

Wednesday, October 10, 2012

SSIS Foreach Loop Container continue or error

A recent question about how to skip a failure inside a Foreach Loop Container , I'm sharing the answer as it's easy and apparently not everybody is aware of it.

So let's assume it's only a simple Foreach Loop Container  that's looping over a files on a network location or even the local disk, getting the file names into a variable and passing it to a file system task to move these files over to another location.

The first step will be by creating an OnError event handler for the file system task, and what I mean by that is

just click on Event Handlers from the top and then click on the BLUE link in the event handlers and that's it :)

 Then look under system variables in the variables window, find the "Propagate" variable and set it to false.

So now The Foreach Loop Container is not aware that the file system task has failed therefore does not cause the container to fail and exit.

OPTIONAL--you can use the failure Precedence Constraint "red branch" to do any other processing, such as to send a notification email with the name of the failed image!!

Hope that helps someone!

Wednesday, August 29, 2012

Skype as a windows update!

Today I got the normal windows update notification in my tool bar, and was surprised that it's showing Skype as an Important Update! I searched for the KB number and found out that's it's now part of windows updates!


Tuesday, August 14, 2012

T-SQL Tuesday #33 – Trick Shot – SSRS User Input

It’s T-SQL Tuesday time again, and this time it's about Trick Shots, my trick today is using SSRS to accept user input!

Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!

The Trick:

1-I started by creating a new project in VS 2008, “Report Server Project” type.

2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”

3-I created a test table called “Employees” contains only 3 columns.

Create TABLE Employees
ID int IDENTITY (1,1),
Name varchar(50),
Comments varchar(MAX)

4- Inserted 4 test records…

Insert Employees VALUES('Jason', 'Lazy Employee')
,('Yousef', NULL)
,('John', 'Anything Goes')
,('Smith', NULL)

5-back to VS, Created a Shared Data Source to my Admin DB

6-created 3 Blank reports, using the shared Data Source.

1- To display the records which in the table.
2- A user summary and confirm screen of which record will be updated and with what.
3- A done screen, which will call a stored procedure or just have a simple inline T-SQL query.

That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value. And also if you need an empty comment, such as to clear the comments field or whatever.

7-The design…

Report1: 1ReportsToDB.rdl
Just a basic table, drag the 3 fields to the table, added an extra field with an expression of =CHR(0254) and Font of: Wingdings Just to display a shape without using an image.
Please refer to my article for shapes in SSRS

Also I added an action to that text field to go to report 2 and pass the ID.

Report2: 2ReportsToDB-Confirm.rdl

Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of: =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.

Report3: 3ReportsToDB-Done.rdl

I added 3 text fields, but it’s all optional (for fun).

Just with an action to go back to the first report and the summary or what was done!!

Lets’ run and test it….

I’ll click next to my name on the check mark to update my comments…
That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.

Remove the NULL check mark and enter the new comments….

You’ll get the confirmation above…
Now click on the check mark to confirm the change, yes you guessed it right…it’s the wingdings shapes again ¿
And that’s what you get..

I’ll click on my GO Back, to the first report to check the data.
Done.. my record was updated in the DB.

Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part.

What I Learned

 that it could get more complicated and done more professionally using Custom .NET code, functions or even a simple stored procedure that knows to delete or update or even insert new record based on a flag.

Hope that helps someone…

The project can be downloaded from


Loop over .sql files using SSIS

This is fairly easy and simple, the requirement is to loop over a folder full of .sql files, that has some queries and run them on the server!

This could be a stress testing queries, create multiple object or drop and recreate indexes queries as in my actual requirement.

We'll need a "For EachLoop container "to iterate over the folder, read the files' location into a STRING variable.

Add a "Execute SQL Task" inside the container, setup the SQL server connection, then setup the query to be from a file connection.

create the file connection, pointing to one of the files.
set the delay validation to true, then setup an expression over the Connection String property.

That's it!!

The package can be downloaded at:
Hope that helps someone...

Thursday, August 2, 2012

Attach AdventureWorks 2012

Quick and easy, but I see people in forums are asking about it...
You may download the .MDF file from here:
I'd typically download and save it with the rest of the data files, maybe at the default location or any other drive if you prefer.

The default location would be:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

Then, right click on your DATABASES under your server name and select ATTACH

Then you'll get the following screen, where you would normally navigate to the download directory and select the advntureworks2012.mdf file.

After you click on OK, you will see that the log file is missing and SQL server will display "Not Found", just highlight the log file and click on remove, then OK.

You're done!! attached ans ready for testing...