Another BI & Programming Blog - Jason Yousef

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.c om/en-us/l ibrary/cc9 66529.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 ther...

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

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_DBTIMESTAMP)(SUBSTRING([date string], 7, 4) + "-" SUBSTRING([date string], 1, 2) + "-" + SUBSTRING([date string], 4, 2))

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

Pinal Dave has a huge list of qestions for the DBA interview questions. http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-complete-downloadable-list-day-0-of-31/

How to control the success and failure of the package and execute it successfully?  Sometimes the requirement is to continue the execution even if an error has raised, might be a package that has nondependent tasks. There are 3 ways to do that… 1- Setting up ‘MaximumErrorCount’ property of the container as ‘0’ instead of the default ‘1’. 2- Setting up ‘ForceExecutionResult’ property to ‘success’ for the task properties instead of the default ‘None’. 3- In tasks sequence, we can set ‘On Completion’ precedence constraint instead of ‘Success’. Hope that helps.