Designspark PCB on two machines under Windows 8.1

I use DesignSpark PCB on my main machine for designing schematics and circuit boards. Recently I’ve set up a new, small PC for use just in my workshop. The setup is exactly the same as the main machine, the user, paths, environment and so on. My documents and projects reside inside the SkyDrive folder. As soon as the account was created the entire folder synced. Then I installed the DesignSpark PCB. I also wanted it to use the same paths as the copy on the main PC. For some reason it could not overwrite the existing files. I pressed “Ignore” as the files already existed so it shouldn’t be a problem. The program launched OK but it refused to open my project files presenting an unknown error.

I was just about to give up with the conclusion that DesignSpark doesn’t like such environments when I remebered something what might be the cause.

Integrated with Windows 8.1 Skydrive uses offline files to save space on mobie devices. A file exists on the drive but the content is in the cloud until you open it. Then it gets downloaded. You can also force the download by selecting “Make available offline” option from contextual menu. After performing this step all the problems were gone.

MPLAB X not launching

While the MPLAB X works great on my home desktop with Windows 7, it won’t launch on my company’s laptop with the same system. The most probable reason is that my account is regulated by my company’s group policies. To fix the problem I’ve found the solution that worked for me. It is to make it to use specified user directory by adding the following parameter to its short-cut target:

“C:\Program Files (x86)\Microchip\MPLABX\mplab_ide\bin\mplab_ide.exe” –userdir C:\Users[your account name]\MPLAB

SSIS Derived Column - US to SQL date

CSV is great but painful sometimes. In one of my SSIS work-flows I had to import data from a CSV file that used US date format without leading zeros:

M/D/YYYY 

It is not the most effective and best method but I used Derived Column component to replace the column. The [date filed] is imported as string [DT_STR]. Then I used the long formula to do some string operations.

First is to add leading zeros to month:

FINDSTRING([date field],“/”,1) < 3 ? “0”:“”

If the first slash is on third position then the month is greater than 9 - no need for zero. Otherwise the zero will be added to the string representing the month.

For adding the zero to day:

FINDSTRING([date field] ,“/”,2) - FINDSTRING([date field] ,“/”,1) < 3 ? “0” : “”

It’s just counting the characters between both slashes.

The rest is just repositioning the date parts. Since the date string is not of fixed length I had to use the FINDSTRING to determine where the date parts start and their length.

SUBSTRING function does the job of extracting the parts:
SUBSTRING([date field], where to start, how many chars)  

Year: SUBSTRING([date field], 5, 4)

But the year may start at 5, 6 or 7th position. That’s why it has to be replaced by the function:

FINDSTRING([date field] ,“/”,2)+1which is just finding the position of the second slash and adding 1. So now it becomes:

SUBSTRING([date field],  FINDSTRING([date field] ,“/”,2)+1 , 4)

Next is the month: SUBSTRING([date field], 1, 1)

But we don’t know if it’s just one or two characters. So we replace the second 1 with the formula:

FINDSTRING([date field],“/”,1) - 1which gives us the position prior to the first slash. We end up with the formula for month:

SUBSTRING([date field], 1,  FINDSTRING([date field],“/”,1) - 1 )

The day is most complex as it lies in the middle and has flexible length. So we need to find both, the start and the length:

SUBSTRING([date field], FINDSTRING([date field],“/”,1) + 1, FINDSTRING([date field ],“/”,2) - (FINDSTRING([date field],“/”,1) + 1)

Finally it’s just adding the strings, slashes, leading zeros and casting the result as date (and adding check in case the value is null):

TRIM([date field]) == “” ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING([date field],FINDSTRING([date field],“/”,2) + 1,4) + “/” + (FINDSTRING([date field],“/”,1) < 3 ? “0” : “”) + SUBSTRING([date field],1,FINDSTRING([date field],“/”,1) - 1) + “/” + (FINDSTRING([date field],“/”,2) - FINDSTRING([date field],“/”,1) < 3 ? “0” : “”) + SUBSTRING([date field],FINDSTRING([date field],“/”,1) + 1,FINDSTRING([date field],“/”,2) - (FINDSTRING([date field],“/”,1) + 1)))

Complex? Sure but it works!

Note from MSDN:
FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type