Thursday, November 17, 2011

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

Friday, November 4, 2011

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

Wednesday, November 2, 2011

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