SSRS check for NULL or Empty String

Alright..I have a column called "Comments" and need to hide that column if it's NULL or empty or have spaces!

Solution:

Just set the visibility expression to:

=IIF(Fields!Comments.Value = "", IIF(Fields!Comments.Value is nothing, IIF(Len(Trim(Fields!Comments.Value)) = 0,True, False), False), False)

Submitted a SQL Tribal Knowledge abstract

Jen McCown ( Twitter | Blog ) is calling all the unpublished SQL Server folk and non MVPs!

She has this brilliant idea about a community-written book of tribal SQL Server knowledge.
http://www.midnightdba.com/Jen/2011/11/sql-tribal-knowledge/

Anyway I've managed to contact her and she was generous to accpet my 3 abstracts after she closed the submition timeline.
I'm So excited that I could write for the SQL community and share the knowledge,These are actually what came to my mind back then, but really regret not sending anything about SQL 2012 as it would be more suitable.

Title: SSIS notifications, using “SEND MAIL TASK”.

Category: Advanced SSIS

Level: 300

Goal: Learn about the various ways and the capabilities of SSIS notifications.

Abstract: Examining the common and the possible ways to send e-mail notifications for various scenarios, on error, on success using the built in “Send Mail Task” and learning abut it’s limitations, the ways to extend it using a script component utilizing a .NET code, sending HTML e-mails, sending Table results to E-mail, using expressions in description and e-mail body, addressing the way to minimize creating notifications by creating a portable template that could be plugged to any package using child / parent variables.

A-Overview, usage and its limitations

B-Using .NET in a Script Component.

C-Using add-on community tasks.

D-Sending HTML E-mail notifications.

E-HTML Table Results.

F-Expression driven dynamic notifications.

G-Creating portable notification templates using variables.

Title: SSIS Dynamic capability, using variables and expressions.

Category: Advanced SSIS

Level: 400

Goal: Learn about the power of dynamic SSIS using expressions and the ways to pass values across components using variables.

Abstract: Learning about moving your package to the next level and leverage the usage by utilizing variables and the impressively expressions that can change and configure itself at runtime.

Get to know the best naming conventions for your variables, how variables are being created, it’s scope and its various data types.

Using variables in your script task, using dispensers, passing variables to a child package.

A-Overview, usage and scope.

B- Variables data Types.

C-Naming best practices.

D-Variables Using .NET in Script Task.

E-Expressions and Dynamic SSIS

F-Using the expressions builder and the Expression Tester App.

G-Operators, Functions, Literals and Casting.

H-Commons Sting, Numeric, NULL, Conditional and Date & Time examples.

Title: SSIS SMO Tasks (SQL Server Management Objects)

Category: Advanced SSIS

Level: 200

Goal: Do you know how many times you’ve used SSIS and you’re not aware! While using the import and export wizard within SSMS, while scheduling maintenance plans within SSMS.

Learn about the various tasks that could help the DBA to make their work faster, Move, copy, detach databases, logins and SQL agent jobs between servers.

Move, copy database objects such as tables, views and stored procedures within tables.

A-   Transfer Database Task.

B- Transfer Error Messages Task

C-   Transfer Jobs Task

D-   Transfer Logins Task

E-   Transfer Master Stored Procedures Task

F-    Transfer SQL Server Objects Task

SSRS can be also used for user inputs!

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028

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

And that’s what I was bargaining about…

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”
CREATE DATABASE [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 http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html

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, it could get more complicated and done more professionally using Custom .NET code and functions.

Hope that helps someone…

The project can be downloaded from
http://www.box.com/s/sskhxfppful1l7sbytzh

References:
http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
http://www.purplehell.com/riddletools/wingdingschart.htm

Thanks
Jason Yousef

Search every table and every CHAR columns for a keyword!

I needed to search for a specific keyword and didn't have the chance to know what's the table's name or even what's the column's name!!

Just replace the KEYWORD in the below query to your needs.

DECLARE @Schema varchar(3)
DECLARE @Table Nvarchar(max)
DECLARE @Column varchar(30)
DECLARE @Keyword Nvarchar(30)

SELECT @Keyword = 'BMAC2120-01'

DECLARE Curs CURSOR FAST_FORWARD FOR

SELECT  s.name As [Schema], o.Name AS [Table],c.Name AS [Column]
FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.type = 'U' and c.collation_name IS NOT null
ORDER BY o.Name,c.Name

OPEN Curs 

FETCH NEXT FROM Curs  INTO @Schema,@Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Table = 'SELECT top 5 [' + @Column + '],''' + @Table + ''' as [Table Name] from ' + +@Schema +'.' + @Table + ' where [' + @Column + '] LIKE ''%' +@Keyword +'%'''
print @Table

FETCH NEXT FROM Curs  INTO @Schema,@Table, @Column
END

CLOSE Curs
DEALLOCATE Curs

T-SQL Combine Date with Time

I've a case where I have 2 columns, Date and Time.

Date column's datatype is DATETIME
Time Column's datatype is INT


Example:

Date:  2009-04-14 00:00:00.000
Time:  can be 743  or  0834

I need to combine them in one DATETIME column.

Select 'DTColumn' = CASE WHEN len(convert(varchar(12),Time)) = 3
     THEN cast(CONVERT(char(8),Date, 112) + ' ' + 
     convert(varchar(10),Substring(convert(varchar(12),Time, 108),1,1) + ':' + right(Time, 2) + ':00'
     ,108) AS datetime)
     WHEN len(convert(varchar(12),Time)) = 4
     THEN  cast(CONVERT(char(8),Date, 112) + ' ' + 
     convert(varchar(10),Substring(convert(varchar(12),Time),1,2) + ':' + right(Time, 2) + ':00'
     ,108)  AS datetime)
   
     End
from Table

That's it...Hope it helps someone...

Error rows, Code and Description in SSIS

1-double click the OLE DB destination to bring it's properties.

2-go to "Error Output" as highlighted in the screenshot, "select "Redirect Row" from the drop-down menu. that will redirect the bad rows to the output.

3-Add a 'script Component task' , go to the input columns, select ONLY  'Error Code" and 'ErrorColumn'., add an OUTPU column to hold the error description.

4-Go to the script screen, and add the following script, make sure to US VISUAL BASIC 2008 as your input language.

5-paste that script..

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub
End Class

6- now you'll have 3 columns, the error code, error row and the description of the error in english.

7-connect a FLAT FILE DESTINATION to the script component so you can captuure the oputput to text, or connect another OLE DB DESTINATION to collect to a database.

Hope that helps someone...

SFTP in SSIS

You have couple of options...

Free option will be a script task to an external WinSCP app, explained in detailes here:

http://www.codeproject.com/KB/database/SSIS_SFTP.aspx


Paid options:

1-http://www.cozyroc.com/   $399.95
2-http://www.rebex.net/sftp.net/  $349.00
3-http://www.nsoftware.com/ssis/   $249.00
4-http://xceed.com/FTP_NET_Features.html   $899.95 USD

Get excel sheet names in a script taks

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
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing  'var dispenser
        Dts.VariableDispenser.LockForRead("User::StrExcelFilePath")
        Dts.VariableDispenser.LockForWrite("User::StrExcelSheetName")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim totalWorkSheets As Excel.Worksheet
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""
        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(Vars("User::StrExcelFilePath").Value)
        ' this code gets the names off all the worksheets
        For Each totalWorkSheets In objWorkBook.Worksheets
            'ExcelSheetName += totalWorkSheets.Name
            Vars("User::StrExcelSheetName").Value = totalWorkSheets.Name.ToString
            MsgBox(totalWorkSheets.Name.ToString)
        Next totalWorkSheets

        Dts.TaskResult = ScriptResults.Success
        Vars.Unlock()
    End Sub
End Class

Hope that helps someone....

Strip Time out of date SSRS

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

Visual Studio 11 Ultimate Developer Preview

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

SSRS - An item with the same key has already been added

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 * !!!

SQL Server 2005 Express Tools is blocking SQL 2008 R2 upgrade!!

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

SSRS Day name of Date Time

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

TypeGuessRows for excel! SSIS Excel import NULLS !

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 number of rows which is appropriate.


Related MS KB:
http://support.microsoft.com/kb/189897

Hope that helps someone as it helped me.

SSRS Formatting

SSRS is full of treasures, I recently came accross that great article and wanted to share it..
http://sqlsafety.blogspot.com/2010/02/ssrs-formatting.html

Enjoy!

SSIS Process files that has been downloaded in the last hour

A recent question on SQL Server Central was asking about how to process the last hour downloaded files.

I think it's fairly simple using a script task to get the files creation date property, and then added a constant constraint to check the difference between now and the creation date, then pass through to the data flow task to process the file.

I've created 6 variables, I'm here getting more usable file properties, but you don't have to do that, I'm just here showing the concept more than doing the exact time difference.

Add a "foreachloop Container" and a "script task" inside.

edit the "Foreachloop Container" location to your file system location. (the one that has your files).

Map the 'FilePath' variable to the output of the "foreachloop"

Edit the "Script Task" ,I'm using VB.net language here.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO.File
Imports System.IO.FileInfo
<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
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing  'var dispenser
        Dts.VariableDispenser.LockForRead("User::FilePath")
        Dts.VariableDispenser.LockForWrite("User::FileSize")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileAge")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim f As IO.FileInfo = New System.IO.FileInfo(CStr(Vars("User::FilePath").Value.ToString))
        Dim fileSize As Double = f.Length
        Dim fileDate As Date = f.CreationTime
        Dim fileLastAccess As Date = f.LastAccessTime
        Dim fileLastMod As Date = f.LastWriteTime
        Vars("FileSize").Value = fileSize / 1024   'get file size
        Vars("FileCreationDate").Value = fileDate
        Vars("FileLastAccessedDate").Value = fileLastAccess
        Vars("FileLastModifiedDate").Value = fileLastMod
        Vars("FileAge").Value = CInt(DateDiff(DateInterval.Minute, fileDate, Now()))
        'only for testing to visualize the results, comment out
        MsgBox("File Name: " & f.FullName & vbCrLf & _
                "File Size: " & Vars("FileSize").Value.ToString & vbCrLf & _
                "File Date: " & Vars("FileCreationDate").Value.ToString & vbCrLf & _
                "File Age: " & Vars("FileAge").Value.ToString)

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

That's it....

Test it and you'll get results like that..

Now use a  precedence constraint to check the file's age and process the less than 60 minutes.

The package could be downloaded from: http://www.box.net/shared/dgyb5z79bgdgdp9pkpi3

Hope that helps someone...

SSRS Data-driven subscription E-mail is not an option

Like the title is saying, when you attempt to create a data-driven subscription in SSRS, E-mail is not one of the listed options.

That means you've not configured a SMTP server yet !

Start -> All Program -> Microsoft SQL Server 2008 / R2 -> Configuration Tools -> Reporting Service Configuration Manager

Then the option will be Appear as normal.

Hope that helps.

Recent Horrible story with my toshiba Laptop!!!

Recently I bought a Toshiba laptop from best buy and that was my biggest mistake, everybody warned me from Toshiba but I didn't listen....  the hard drive crashed after 3 months and it was a nightmare over the phone with them for hours so they can understand what I'm trying to say !!!!!

and the guy over the phone kept asking "did you pack your data before shipping the laptop" and he's processing a repair for a crashed hard drive...

Also to mention that you'll have to pay $25 so they can provide you with a UPS box and pre-paid shipping label.

The whole process took 2 weeks to call, send and receive.....

The bottom line that they're cheap company that doesn't stand by their product and by their customers, they dont' care about the customer to leave them without a laptop for 3 weeks!!

and you pay to get your stuff fixed while it's still under the warranty !!

That's only my personal experience and I'm sharing it for knowledge....

SSIS Performance considerations

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternative technique you could gain much more performance out of your SSIS flow.

The recommendations and suggestions below represents real life experience and excerpts from the Microsoft Technet article : http://technet.microsoft.com/en-us/library/cc966529.aspx along with influence from the other links referred to at the bottom of this Article. While this Article presents a summary and major points to focus on from those resources, you are encouraged to follow the links for more specific and in depth discussion.

SSIS (SQL SERVER INTEGRATION SERVICES) is using a buffer-oriented architecture to efficiently load and manipulate datasets in memory, the benefits is avoiding the I/O to the disk and physically copying the data to the disk, and therefore it’s transferring the data from the source to the destination through the pipeline without touching the disk.

As this data flows through the pipeline, SSIS attempts to reuse data from prior buffers as much as possible when additional operations are performed. How buffers are used and reused depend on the type of transformations that you use in a pipeline.

There’re 3 types of transformations types:

1-Row Transformation (synchronous transformation) also known as (Non-blocking transformations):

These process the data in Row-by-row basis, Row transformations have the advantage of reusing existing buffers and do not require data to be copied to a new buffer to complete the transformation.

• Do not block data flow in the pipeline.
• Data is not copied around, only pointers.

Examples:

• Audit
• Cache Transform
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Derived Column
• Export Column
• Import Column
• Lookup
• Multicast
• OLE DB Command
• Percentage Sampling
• Script Component
• Slowly Changing Dimension

2-Partially Blocking Transformation (asynchronous transformation):

Are often used to combine several datasets. They tend to have multiple data inputs. As a result, their output may have the same, greater, or fewer records than the total number of input records.

• Introduces new buffers in memory layout.
• Transformed data is copied into new buffers.

Examples:

• Data Mining
• Merge
• Merge Join
• Pivot
• Unpivot
• Term Lookup

3-Blocking Transformation (asynchronous transformation):

Blocking transformations must read and process all input records before creating any output records. Of all of the transformation types, these transformations perform the most work and can have the greatest impact on available resources.

• Must see all data before passing on rows.
• Blocks the data flow – can be heavy on memory
• May also use “private buffers” to assist with transforming data.

Examples:

• Aggregate
• Fuzzy grouping
• Fuzzy lookup
• Row Sampling
• Sort
• Term Extraction


Evaluating the performance:

1-You could monitor and observe the performance using the PROGRESS tab, while running the package, it’ll tell you when the task started, ended and how long it’s taking to finish, which would be a great way of discovering which tasks is pulling the performance down.

progressTab.png (42 KB) (File Type Details)

Progress Tab

2-You could also use a free add-on for BIDS (Business Intelligence Development Studio) called BIDS HELPER, can be downloaded from http://bidshelper.codeplex.com/

It offers whole new features for the BIDS environment which would help you such as deploying your packages, dtsConfig file formatting and SSIS performance Visualization which I’ll cover now.

Using the SSIS performance Visualization is simple, you just right click on your package after installing the BIDS HELPER and you’ll have new options, you need to select “Execute and Visualize Performance”.

bidsw.png (30 KB) (File Type Details)

Bids Helper

You’ll be introduced to another screen with Performance Tabs, and you choose to visualize the performance by looking at Gantt Bars, Statistics Grid or Statistics Trend, which compares the performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.

SSISPerformanceTrendGrid.png (16 KB) (File Type Details)

Performance Trend

3-Using Perfmon (formerly known as Windows Performance Monitor and synonymously referred to as Perfmon)

There're several counters to monitor SSIS performance, such :

SQLServer:SSIS Service:

SSIS Package Instances - Total number of simultaneous SSIS Packages running

SQLServer:SSIS Pipeline:

BLOB bytes read - Total bytes read from binary large objects during the monitoring period.
BLOB bytes written - Total bytes written to binary large objects during the monitoring period.
BLOB files in use - Number of binary large objects files used during the data flow task during the monitoring period.
Buffer memory - The amount of physical or virtual memory used by the data flow task during the monitoring period.
Buffers in use - The number of buffers in use during the data flow task during the monitoring period.
Buffers spooled - The number of buffers written to disk during the data flow task during the monitoring period.
Flat buffer memory - The total number of blocks of memory in use by the data flow task during the monitoring period.
Flat buffers in use - The number of blocks of memory in use by the data flow task at a point in time.
Private buffer memory - The total amount of physical or virtual memory used by data transformation tasks in the data flow engine during the monitoring period.
Private buffers in use - The number of blocks of memory in use by the transformations in the data flow task at a point in time.
Rows read - Total number of input rows in use by the data flow task at a point in time.
Rows written - Total number of output rows in use by the data flow task at a point in time.

But basically you need to monitor the 3 famous kinds of memory buffers:

"Buffers"
"Private Buffers"
"Flat Buffers"

Read More....


Evaluating Design and performance considerations:

As I always say, everything is life could be done in many different ways; it’s just YOU who needs to decide which approach is better for you as it really depends on your environment and business requirements.

I’m going to point some points that you’ll need to do more research and also included some links so you can look it up in more depth.

1-Remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that needs a new buffer.

2-Break complex ETL tasks into logically distinct smaller packages. More...


3-Maximize Parallelism. Utilizes the available resources as much as you can. More ...

4-Maximum insert commit size and Rows per batch has a great effect for not treating the whole incoming rows as one patch.

5-Minimize staging as possible otherwise use RawFiles. More ...

6- Eliminate redundant columns:

• Use SELECT statements as opposed to selecting the tables dropdown.
• Be Picky! Select only what columns you need VS SELECT *

7-Minimize blocking as much as you can:

• Sort your query in the engine, or even using the SQL command in OLE DB Source instead of using Sort transformation.
• Merge transform requires Sort but not Union All, so use Union All wherever you can. More ...

8- Optimize the BufferTempStoragePath and BLOBTempStoragePath wisely. More ...

9-For Flat File Source use FAST PARSE option for columns of integer and date data types. More ...

10- If the SQL server is local, consider using SQL Server destination instead of OLE DB Destination.

11-Consider indexes fragmentation and performance when inserting high volume of data.

12-Use “Fast Load” when using OLEDB, it uses BULK INSERT and would be faster.

13-Optimize the packet size in your connection managers to 32K (32767) and that’s the fastest option.

More resources on the subject:

Top 10 SQL Server Integration Services Best Practices

Designing and Tuning for Performance your SSIS packages in the Enterprise (SQL Video Series)

Integration Services: Performance Tuning Techniques


That's it...I hope my article helps someone gaining more performance from their ETL solution, and feel free to add more performance considerations

Last Day of the Month SSRS Expression....

A question came on one of the fourms about how to get the last day of the month using months as intgers, the person has 1,2,3....etc in his column and need to convert that to month and get the display only the last day of the month in the MM/DD/YYYY format....

Use that for January (1)

=Left(DateSerial(Year(Now()), 1, "1").AddMonths(1).AddDays(-1) , 10)

Use that for February (2)

 

=Left(DateSerial(Year(Now()), 2, "1").AddMonths(1).AddDays(-1) , 10)

 

Hope that helps...

Derived Column to convert string to DB Date.

I have a date field in string format "0MMDDYY" and some value has "0000000".
I need to convert the string  to normal db date and also replace "0000000" with NULL.

I'm using a derived column, and assuming that the column name is [Column].

[Column] == "0000000" ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

and if it was an INT field....

[Column] == (DT_WSTR,7)0000000 ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

That's assuming that your dates are in the year 2000 and above...

What if the String has a date such "0110540"

The above code will give you 2040-11-05

so we'll have to check and fix the year to 1940

@[User::Column] == "0000000" ? "NULL"  :    (   (DT_I4)(DT_WSTR,2) RIGHT(@[User::Column],2)  <= 11  ? ((DT_WSTR,2)20 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2)   )  :    ( (DT_WSTR,2)19 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2)  ))

That's the only way since we have only 2 digits for the year, plus i'm calculating patient's age, so I don't think any of them over 100 years old !

I'm open to discussion if you have a better way of doing it..

Hope that helps..

SSIS "MM/DD/YYYY" Format expression.

(DT_DBTIMESTAMP)(SUBSTRING([date string], 7, 4) + "-" SUBSTRING([date string], 1, 2) + "-" + SUBSTRING([date string], 4, 2))

FTP files list to text file using SSIS.

I got a requirment to dump all the FTP file listing for all folders and subfolders to a text file.
I'm using here a free FTP server which you could use too
ftp://ftp.secureftp-test.com/            User:"test"    Password: "test")     
                
what SSIS FTP Task can you do nativly?

The FTP Task, supports, sending, receiving, deleting folders and directories, but doesn’t support watching for a specific files or getting a list of the FTP directory listing.

Get list of files and folders to a text file.

 A.Drag a script task 

B. Select “Microsoft Visual Basic 2008” as the scripting language, then click “Edit Script”

C. Add the following script

And the result will be at D:\FTPList.txt



Hope that helps!!

Download the package from http://www.box.net/shared/77tqeitp99czj6feefxv

Imports System

Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.IO

<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
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

    Public Sub Main()
        Dim FilePath As String = "D:\FTPList.txt"      'save to location - You can use a variable here.

        'delete if file exists, Comment out if you need to append to the existing TXT file

        If File.Exists(FilePath) Then
            File.Delete(FilePath)
        End If

        Try

            'Create the connection to the ftp server
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
            Dim Folders As String()
            Dim Files As String()
            Dim FileName As String
            Dim Folder As String
            Dim Dir As String

            'Set the properties like username & password

            cm.Properties("ServerName").SetValue(cm, "ftp://ftp.secureftp-test.com/")          'Server address
            cm.Properties("ServerUserName").SetValue(cm, "test")                        'user name
            cm.Properties("ServerPassword").SetValue(cm, "test")                        'password

            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

            'Connects to the root of the ftp server,

            Dir = "/"                                'root directory
            ftp://ftp.connect/()
            ftp://ftp.setworkingdirectory(dir/)
            ftp://ftp.getlisting(folders/, Files)

            Dim SW As System.IO.StreamWriter  'Writer to write to the file

            SW = System.IO.File.AppendText(FilePath)

            'Write date time header (getting fancy)

            SW.WriteLine(Now().ToString + vbCrLf + vbCrLf + vbCrLf)

            'write root file list

            SW.WriteLine("Folder : " + Dir + vbCrLf + "----------------------" + vbCrLf)
            If Not Files Is Nothing Then  'checking for no files
                For Each FileName In Files
                    SW.WriteLine(FileName)
                Next
                SW.WriteLine(vbCrLf)
            End If

            'write other folders and their files' list

            If Not Folders Is Nothing Then 'checking for no other folders
                For Each Folder In Folders
                    SW.WriteLine("Folder : " + Folder + vbCrLf + "----------------------" + vbCrLf)
                    Dir = "/" + Folder
                    ftp://ftp.setworkingdirectory(dir/)
                    ftp://ftp.getlisting(folders/, Files)

                    If Not Files Is Nothing Then 'checking for no files

                        For Each FileName In Files
                            SW.WriteLine(FileName)
                        Next
                    End If
                    SW.WriteLine(vbCrLf)

                Next

            End If
            'Close ftp connection
            ftp://ftp.close/()
            'Close writer
            SW.Close()

        Catch ex As Exception 'Catch errors, make up your own error as you can see.

            Dts.Events.FireError(911, "", "Errrrrrrrrrrrrrrrrrror, something bad happened!!!", "", -1)
            Dts.TaskResult = ScriptResults.Failure 'reprot failure

        End Try

        Dts.TaskResult = ScriptResults.Success 'report success
    End Sub
End Class