Special Characters
It might be hard to work with special characters in your data. For instance, doing a replace on a field that looks like it has a blank (but is actually is carriage return) won’t yield the results you are seeking. Instead, replace your special characters using the char function:
char(9) : tab
char(10): line feed
char(13) carriage return
If you want to find the others use this query:
SELECT ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char),50))
,ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char)+1,50))
FROM @tbl
Or if you are looking for a function I found this better approach from here by Christian d’Heureuse:
– Removes special characters from a string value.
– All characters except 0-9, a-z and A-Z are removed and
– the remaining characters are returned.
– Author: Christian d’Heureuse, http://www.source-code.biz
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(256)
set @s2 = ”
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
Thank you Christian for this tremendous help! (script from http://www.source-code.biz/)
(I only copy the script here in case Christian decides to take down his website as this has happened to me in the past)
SSIS file name with time
There is often a need to add a time stamp on your file. On your file connection (in connection manager section of ssis) pull up the properties for your file (F4 or right click…properties). Expand expressions and add in a new expression of ‘Connection String.” From there you should be able to get to your expression builder. This uses regular expressions.
From there add your file location, file name and then you can add the parts of your dates with
(DT_WSTR,20) DATEPART("Hh", GETDATE()) +"_" + (DT_WSTR,20)DATEPART("n", GETDATE()) +"_" + (DT_WSTR,20)DATEPART("s", GETDATE()) +".txt"
DT_WSTR is the casting function and DATEPART pulls out the part of your date.
Result: 8_23_31.txt
batch-stamp current time and date
Alas, I’ve broken my rule to not use batch.
The way I create a file stamping the current date and time is as follows
@ECHO OFF
FOR /F "tokens=*" %%A IN (‘TIME/T’) DO SET Now=%%A
set strFile=%date:~10,4%%date:~4,2%%date:~7,2%_%Now%
set strFile=%strFile%
set strFile=%strFile::=%
set strFile=%strFile: =%
@ECHO ON
dir >>c:\%strFile%_CUSTOMgetfromsvn.txt
Assistance received from….
Package Event Handler
A package level event handler is not just an event handler for the package. It is also an event raised for each executable inside the package. Restated: if you place an event handler and place it at the top node (at the package level) this will occur for your package and every executable inside of it.
The problem: I thought that I could throw an insert to a table to discover when my package completed. To do this …at the package level……I set the ‘on post execute’ handler to include an insert statement with the end time. However, it inserted twice (when there was only one object). Once for the object, once for the package.
The solution: I created a constraint (from an empty container) with a test on the variable System::SourceParentGuid. When it is empty, I know that it is not coming from a parent executable, and from the package only. The constraint was @SourceParentGUID=="" Now my insert will only execute on post execute when there isn’t a source parent GUID.
Single Quotes in Outlook to SQL. subtitle: how to go from curly to strait
I’ve long wished that the single quotes in outlook and word would easily translate to SSMS. On my blog, in my emails, in word, on my bathroom wall, all over the place, you can find the single quote that doesn’t work in the query window. Copy and paste and replace the quotes with quotes. I’m not sure who had the bright idea to make single quotes fancy. Possibly someone with a little more time on their hands. They probably had a mustache.
Regardless, all my whining took a nose dive when I discovered that there is actually an option to not use those fancy curly single quotes.
WHAT?
Yes. Deep in the cavities of options you can find a way to keep outlook, word, and others from replacing the normal single quotes to fancy ones.
-
- curlystrait1
-
- curlystrait2
-
- curlystrait3
SSIS Variables Can be changed in Job Step
I wanted to make note that if you want to set values to variables one way to do this is via the sql job. You do this in the Set Values Tab (job properties, step properties, set values tab).
Set the property path to your custom VARIABLE_NAME
\Package.Variables[User::VARIABLE_NAME].Properties[Value]
Set the value to your custom value. The advantage of doing this, of course, is being able to dynamically change a value instead of changing the source. For example, I like to set up email messaging here instead of hard-coding inside the application:
Property Path:
\Package.Variables[User::EmailErrorsTo].Properties[Value]
Value:
user@company.com
Prescedent Constraint Missing Prescedent Constraint
For some reason my expression on my prescedent constraint wasn’t passing successfully. Restated: I have a constraint on a data flow item to test an expression. I have it testing a string value that I set in the script editor. If it is not empty I send an email. The expression I was using (@[User::EmailRowErrors] !=”" or @EmailRowErrors !=”" Either one was working ) .
For some reason it stopped working. Worked before. Doesn’t work now. After some time I learned that the expression editor cannot handle > 4k characters, so it was just bombing because my text was simply too long. I never received an error of any kind, fyi.
The end result is that I created another boolean variable that I also set in the script editor and I test for it and now it works like a champ. (@[User::SendEmail] ==true) Thanks for reading.
