Developing Matt

My Technical Journal

Special Characters

leave a comment »

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)

Written by matt

October 1, 2012 at 3:09 am

Posted in T-Sql

SSIS file name with time

leave a comment »

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

Written by matt

August 17, 2012 at 7:24 am

Posted in Uncategorized

batch-stamp current time and date

leave a comment »

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

http://www.robvanderwoude.com/datetimentexamples.php

http://www.dostips.com/DtTipsStringManipulation.php

Written by matt

August 14, 2012 at 12:24 pm

Posted in Batch

Package Event Handler

leave a comment »

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. 

 

constraintonParent

Written by matt

August 10, 2012 at 8:19 am

Posted in SSIS

Tagged with

Single Quotes in Outlook to SQL. subtitle: how to go from curly to strait

leave a comment »

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.

http://office.microsoft.com/en-us/word-help/change-curly-quotes-to-straight-quotes-and-vice-versa-HA010173242.aspx

Written by matt

March 15, 2012 at 1:07 pm

Posted in Sql Server

SSIS Variables Can be changed in Job Step

leave a comment »

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

Written by matt

February 21, 2012 at 4:15 pm

Posted in SSIS

Prescedent Constraint Missing Prescedent Constraint

leave a comment »

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.

Written by matt

February 21, 2012 at 3:34 pm

Posted in SSIS

Follow

Get every new post delivered to your Inbox.