Another BI & Programming Blog - Jason Yousef

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

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

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

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