SSIS Skip Blank Rows in Flat File Sourcehttps://joellipman.com/articles/microsoft/ssis/ssis-skip-blank-rows-in-flat-file-source.html

23 April 2013 employee number and the second column will be the remaining data on that row. So I setup my connection manager as follows (note the "Ragged Right" and delimiter as "{CR}{LF}" - my rows to skip is because of the state of my data): Crucial fix here, add 2...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Improve Default Joomla Search https://joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 to type a number like "1234" and this returns that specific article. In the case of Joomla, we'll use the article ID. Pre-Notes: This article consists of instructions to hard-code some core files in Joomla. As a precaution, you may want to back up all...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

T-SQL Conversion failed when converting the varchar to data type inthttps://joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on. How? So where does the 'B110' string come from, well...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Autohotkey Count Number of Files/Folders in a Directoryhttps://joellipman.com/articles/automation/autohotkey/autohotkey-count-number-of-files-folders-in-a-directory.html

04 June 2013 "\\$") ; gets rid of trailing slash if required -- Method #1 count := 0 Loop, %UserFolder%\*.*, 0, 1 count++ -- note for future use: ; if A_LoopFileAttrib contains H,R,S ; continue This works fine at home on your local host on a local drive. Try using...

• Type: Article
• Author: Joel Lipman
• Category: AutoHotkey
• Language: en-GB

Split a row into multiple rows based on a column valuehttps://joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 001 1.00 1997-11-17 002 1.00 2000-02-18 002 1.00 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 -- note the dates increment and do not account for days off (eg. Saturday / Sunday) How? Adapted from a forum topic: SQLTeam Forums...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Compare two databases using T-SQLhttps://joellipman.com/articles/database/t-sql/compare-two-databases-using-t-sql.html

24 September 2013 OR t1.COLLATION_NAMEt2.COLLATION_NAME OR ( t1.TABLE_CATALOG IS NULL OR t2.TABLE_CATALOG IS NULL ) -- NOTE: This does not list if a column has been moved to another table Method #4 And a final enhancement to epitomize my laziness (replace [myDB1] with...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Creating a custom form field type for Joomla XML fieldsethttps://joellipman.com/articles/cms/joomla/creating-a-custom-form-field-type-for-joomla-xml-fieldset.html

25 October 2013 projects irrespective of which user is logged in What I want: SELECT * FROM #__projects WHERE user_id= ORDER BY name Please Note: This article focuses on a front-end form dropdown. If you would like to see how to do modals for the admin panel, see my...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Regular Expression Basic Usage Exampleshttps://joellipman.com/articles/web-development/regular-expression-basic-usage-examples.html

28 October 2013 expression matches 'DEF' in the string: abcDEFghi The expression does not return a match for the following string: abcdefghi Note that the character class must occur within a character list, so the character class is always nested within the brackets...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Client Services: Website Development Agreementhttps://joellipman.com/static-items/client-services-website-development-agreement.html

15 November 2013 website hosts with their own support plans and I can help you configure these for the source files (as per my base rate). Note that my servers are based in Houston, Texas, USA (due to their traffic [when europe is active, these are not and vice-versa]...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

Generate a Timesheet in MySQL https://joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 example is using a small table with very little sample data so do not expect it to reconcile with the requirement example. Note that we have two events on the Thursday. A few things we have to deal with still: We have lost the rows with days that have...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

MS-DOS: Copy folders without overwriting fileshttps://joellipman.com/articles/automation/ms-dos/ms-dos-copy-folders-without-overwriting-files.html

23 February 2014 A quick note on how to copy files and folders over without overwriting existing files. It's easy to say "Yes" and replace all files. What I need is something that synchronizes files on an internal hard drive to an external one. I wanted it to copy only...

• Type: Article
• Author: Joel Lipman
• Category: MS-DOS
• Language: en-GB

Registered Users Cannot Login - Super Users Canhttps://joellipman.com/articles/cms/joomla/registered-users-cannot-login-super-users-can.html

26 February 2014 to Joomla CMS v2.5.14 What? This article is a note on how I fixed an issue on a client site. The website had a redirect on the login which would go to a page accessible by "registered" users but only "Super Users" could actually login. "Registered"...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Excel: Find values in one column that are not in anotherhttps://joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new... Why? Consider the...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: *

T-SQL: Parse an XML valuehttps://joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 AS Gender -- Returns records where GENDER = "Male" More Examples (Note that Event_XML is of datatype XML in the following examples): -- exist() returns 1 or 0 if node exists or not respectively SELECT Event_XML.exist('/STAFF/EMPLOYEE_NUMBER') FROM...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Hide a Drive per User in Windows 7https://joellipman.com/articles/microsoft/windows-os/hide-a-drive-per-user-in-windows-7.html

28 March 2014 in MS Windows, you may need to find someone who is. How? As a proof of concept, let's see how to hide a specific drive. Note that this section applies to the currently logged-in user. To restrict other users, see the example after this one: Open the...

• Type: Article
• Author: Joel Lipman
• Category: Windows OS
• Language: *

SSRS Stop Scrolling Behindhttps://joellipman.com/articles/microsoft/ssrs/ssrs-stop-scrolling-behind.html

08 April 2014 design mode of the report (apologies for the red censoring but this allows the images to be used in further demonstrations): Note that a footer exists (contains page number) because if printed, the page would end on a dataset row and continue the next...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Convert to Proper Case in T-SQLhttps://joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 SampleText lowercase.text lowercase.text Examples of usage: SELECT ufn_ProperCase([Surname]) FROM employees Additional: Note how the above doesn't convert postal codes, specifically British postcodes, which isn't included in the examples above because...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

SSIS: How to loop through multiple flat files as data sourceshttps://joellipman.com/articles/microsoft/ssis/ssis-how-to-loop-through-multiple-flat-files-as-data-sources.html

13 June 2014 creating an extract SSIS package intended to take a text file as its source and to populate a database table with this data. Note that this only works if all the text files to be used as source data have the same number of columns and where the column...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Include a carriage return in a column headinghttps://joellipman.com/articles/microsoft/sql-server/include-a-carriage-return-in-a-column-heading.html

07 July 2014 To: MS SQL Server 2008 R2 MS Windows 7 Enterprise (Client) MS Excel 2010 What? A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server
• Language: *

T-SQL functions to convert Strings to Tableshttps://joellipman.com/articles/database/t-sql/t-sql-functions-to-convert-strings-to-tables.html

20 August 2014 to: Microsoft SQL Server 2008 R2 Microsoft SQL Server 2012 What? These were in a solution and I thought I'd note them on my site so I can refer to them more easily. How? Let's start with a function that just converts a string of words delimited by...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB