Another BI & Programming Blog - Jason Yousef

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)

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

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

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

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

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

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