_x003C_none_x003E_ error in SSIS

This is just a quick note.  I was working on an example package, and decided to change environments to make sure that everything was working correctly.

I deployed my example database, then ran the test packages that I have.

I noticed that every file started coming up with weird errors by embedding _x003C_none_x003E_ into the data.  I was not sure what to do about this, so I did a quick check and found that the text qualifiers were set to this in the code.

I did a quick find and replace all for the value and replaced it with empty string, and all of my projects began producing the right outputs.  When I went back to my main development machine, and checked its packages and outputs, I noticed that the _x003C_none_x003E_ was also present in those packages, yet they were working fine.

Well, after some internet google searches, I was able to track down that different versions and different builds of SSIS treat the <none> option in the connection manager differently, and this is what was causing the issue.

So, if you ever see this issue, the "proper" way of fixing it is to:

1.  Make sure every machine is running the same build/version of SQL Server, and thus the same build/version of SSIS.

2. Once everything is normalized, go into the package and simply "choose" none again.  If you are having this problem with fixed width flat files, I found it was possible to go in and simply change _x003C_none_x003E_ to an empty string, and this worked to fix the issue as well.  I wouldn't suggest doing it that way, as it is probably a better practice to have SSIS use whatever placeholder it likes, and will understand, instead of introducing unexpected behavior by editing the file directly.

EDIT:  Here is the best resource I have found on this issue.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ff59e4a1-a2bf-4b27-812b-28197ec982de/

How to determine the rows per millisecond processed by a data flow

If you are doing system testing, and know how many rows are being processed.  The following is a simple piece of code that will look at the SSIS log and give a number on the process.

@PackageSourceID is the ID of the package being executed.
@listsize is the size of the record set being processed.

The package must have logging enabled, and the package must have OnPreExecute and OnPostExecute logged.

DECLARE @PackageSourceID VARCHAR(255)

SET @PackageSourceID = '2142F02C-907D-47A0-B3A5-E0BAC8CB5AEC'

DECLARE @listsize INT

SET @listsize = 1000000;

WITH cte_startend

     AS (SELECT *

         FROM   sysssislog

         WHERE  executionid IN (SELECT executionid

                                FROM   sysssislog

                                WHERE  sourceid = @PackageSourceID)

        --where executionid = @ExecutionID

        ),

     preexecute

     AS (SELECT *

         FROM   cte_startend a

         WHERE  event = 'OnPreExecute'),

     postexecute

     AS (SELECT *

         FROM   cte_startend a

         WHERE  event = 'OnPostExecute'),

     executetime

     AS (SELECT Datediff (ms, pe.starttime, poe.endtime) AS executetime,

                pe.id,

                pe.source,

                pe.starttime,

                pe.endtime,

                pe.sourceid,

                pe.executionid

         FROM   preexecute pe

                JOIN postexecute poe

                  ON pe.sourceid = poe.sourceid

                     AND pe.executionid = poe.executionid

                     and pe.sourceid not in (@PackageSourceID)

)

--@listsize

SELECT executionid,

       @listsize / executetime AS rowsperms,

       *       

FROM   executetime et

ORDER  BY et.executionid DESC,

          @listsize / executetime DESC

SQL Code Snippets - Split String and Examples

The original implementation of the code below was created because of a project I did that involved converting COBOL code into SQL server code.  The problem stemmed from having to emulate COBOL code which would split data based on a character type, and then place the data into columns of a COBOL defined export.  The values could not be duplicated, and we could only grab the first 3 unique values in order.

This means that I had to have a method of easily splitting a string, and then taking the data and grabbing non duplicate data, and ignoring empty strings.  This was a considerably more interesting problem than I originally thought it would be when I first approached it.

So the first step is to get a good split string implementation.  I went with a common one you can get after searching for a second on Google.

CREATE

--DROP

FUNCTION [dbo].[SplitString]

(

      @InputString VARCHAR(MAX),  -- Input string

      @Delimiter CHAR(1) = ',',  -- Delimiter

      @RemoveEmpty bit = 1,   -- Remove Empty IE empty string is removed from the output

      @RemoveDupes bit = 0   -- Remove Dupes -- Removes duplicates from the list if executed.

)

RETURNS

      @OutList TABLE

      (

            Position    int identity(1,1)  -- The position of the returned parsed string

            ,Value VARCHAR(50)                     -- The value of the returned parsed string.

      )

AS

BEGIN

      --Parse the string and insert each list item into the @tblList

      DECLARE @ListItem varchar(max)

      DECLARE @Pos1 int, @Pos2 int, @Length int, @ListLength int

      SET @Pos1 = 1

      SET @ListLength = LEN(@InputString)

      WHILE @Pos1 <= @ListLength

            BEGIN

                  SET @Pos2 = CHARINDEX(@Delimiter, @InputString, @Pos1)

                  IF @Pos2 < 0 SET @Pos2 = 0

                  SET @Length = @Pos2 - @Pos1  

                  IF @Length < 0 SET @Length = 0

                 

                  IF @Pos2 > 0

                        BEGIN

                              SET @ListItem = SUBSTRING(@InputString, @Pos1, @Length)

                              SET @Pos1 = @Pos2 + 1

                        END

                  ELSE

                        BEGIN

                              SET @ListItem = SUBSTRING(@InputString, @Pos1, @ListLength)

                              SET @Pos1 = @ListLength + 1

                        END

                                   

                  IF(@RemoveEmpty = 1 and RTRIM(isnull(@ListItem,'')) = '')

                  BEGIN

                        CONTINUE;

                  END

                  ELSE

                  IF(@RemoveDupes = 0)

                  BEGIN

                        INSERT @OutList (Value) VALUES(@ListItem)

                  END

                  ELSE

                  BEGIN     

                    INSERT @OutList (Value)

                    SELECT @ListItem

                    WHERE NOT EXISTS (SELECT * FROM @OutList where Value = @ListItem)

                  END

      END

      RETURN

END

I then create a problem set of data that I want to split.

tmpexampleid	datatoparse
1	ParseThisValue1,ParseThisValue2,ParseThisValue3
2	ParseThisValue1,ParseThisValue2,ParseThisValue4
3	ParseThisValue1,,ParseThisValue2,ParseThisValue4
4	ParseThisValue1,,ParseThisValue2,ParseThisValue3,ParseThisValue4
5
6	,,,,
7	,ParseThisValue1,,,
8	,ParseThisValue1,,ParseThisValue2,
9	,ParseThisValue1,,ParseThisValue2,ParseThisValue3
10	,ParseThisValue1,          ,ParseThisValue2,ParseThisValue3

And then I do the string split.  Note the following should run as long as SplitString exists in the same database context.

-- EXAMPLE of RETURN

SELECT * FROM [dbo].[SplitString] (

   'test,test2,test3,,,,,, '

  ,','

  ,1

  ,0)

 

  -- No values returned if no data to split!

 SELECT * FROM [dbo].[SplitString] (

   ''

  ,','

  ,1

  ,0)

 

  SELECT * FROM [dbo].[SplitString] (

   null

  ,','

  ,1

  ,0)

  GO

 

 

  -- This shows all options turned on, returns a postioned deduped list that removes empty string values.

IF object_id('tempdb..#tmpexample') IS NOT NULL

BEGIN

   DROP TABLE #tmpexample

END

 

create table #tmpexample

(

      tmpexampleid int identity(1,1)

      , datatoparse varchar(255)

)

INSERT INTO #tmpexample

(

      datatoparse

)

SELECT

      'ParseThisValue1,ParseThisValue2,ParseThisValue3'

UNION ALL

SELECT

      'ParseThisValue1,ParseThisValue2,ParseThisValue4'

UNION ALL

SELECT

      'ParseThisValue1,,ParseThisValue2,ParseThisValue4'

UNION ALL

SELECT

      'ParseThisValue1,,ParseThisValue2,ParseThisValue3,ParseThisValue4'

UNION ALL

SELECT

      ''

UNION ALL

SELECT

      ',,,,'

UNION ALL

SELECT

      ',ParseThisValue1,,,'

UNION ALL

SELECT

      ',ParseThisValue1,,ParseThisValue2,'

UNION ALL

SELECT

      ',ParseThisValue1,,ParseThisValue2,ParseThisValue3'

UNION ALL

SELECT

      ',ParseThisValue1,          ,ParseThisValue2,ParseThisValue3'

     

select * from #tmpexample

;with cte_parsemap as

      (

            select 1 as idpos, 'PRIMARY' as namepart

            UNION

            select 2, 'SECONDARY'

            UNION

            select 3, 'THIRD---ARY'

      ) , cte_candidates as

      (

      SELECT *

      FROM  #tmpexample PPOSRC

      --WHERE

      --    and FileID = @FileID

      )

      , cte_parsenames as

      (

            select

            c.[tmpexampleid],O.Value,cp.namepart

            from cte_candidates c

            CROSS APPLY

            dbo.[SplitString](c.[datatoparse], ',',1,1) AS O

            JOIN cte_parsemap cp

                  on O.Position = cp.idpos

      )

      , cte_parsed as

      (

            select pvt.[tmpexampleid],[PRIMARY],[SECONDARY],[THIRD---ARY]

            from cte_parsenames c

            PIVOT

            (

            max (Value)

            FOR namepart IN

            ( [PRIMARY],[SECONDARY],[THIRD---ARY])

            ) AS pvt

      )

      SELECT *   

      FROM cte_parsed  

     

GO

Example Output

1	ParseThisValue1	ParseThisValue2	ParseThisValue3
2	ParseThisValue1	ParseThisValue2	ParseThisValue4
3	ParseThisValue1	ParseThisValue2	ParseThisValue4
4	ParseThisValue1	ParseThisValue2	ParseThisValue3
7	ParseThisValue1	NULL	NULL
8	ParseThisValue1	ParseThisValue2	NULL
9	ParseThisValue1	ParseThisValue2	ParseThisValue3
10	ParseThisValue1	ParseThisValue2	ParseThisValue3

SQL Code Snippets - Sample Simple Cursor

For those of you, like me, who like to have a piece of working code sitting around that I can easily template off of.  Here is my example for a simple cursor that can easily be manipulated if you absolutely, positively, cannot avoid using a cursor in SQL

Create table #tmp

(

      myid int identity(1,1)

      , avalue varchar(255)

)

insert into #tmp

(

      avalue

)

SELECT

      'test1'

UNION

SELECT 'test2'

UNION

select 'Messag3'

DECLARE @myid int, @message varchar(255)

PRINT '-------- quick print --------';

DECLARE mycursor CURSOR FOR

SELECT  myid from #tmp

OPEN mycursor

FETCH NEXT FROM mycursor

INTO @myid

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT ' '

    SELECT @message = 'ID PRINT: ' +

        cast(@myid as varchar(255))

      PRINT @message

     

        SELECT @message = (select avalue from #tmp where myid = @myid)

       

        PRINT @message

        -- Get the next id

    FETCH NEXT FROM mycursor

    INTO @myid

END

CLOSE mycursor;

DEALLOCATE mycursor;