Using SSIS to transfer a CSV file that contains SQL (i.e., potential special characters) in it

Question

Using SSIS to transfer a CSV file that contains SQL (i.e., potential special characters) in it

Using SSIS to transfer a CSV file that contains SQL in it.

I'm using .NET to create a CSV file that I then transfer to a table using an SSIS package.

The contents of the file is a 36 char GUID and any SQL text which could contain tabs characters, pipe chars and probably any type-able character. I thought I would specify my own column and row delimiters using the Windows CharMap accessory utility to select non type-able chars for delimiters. I selected ¼ and ½ as the col and row delimiter, respectively.

The test file I created looks like this:

Guid¼Sql½3afc912b-917d-4719-8ded-22e5d95930a3¼SELECT 
 * FROM 
 TABLE½a867fa30-f2c7-459e-8985-9ef42616991e¼SELECT 
 * FROM 
 TABLE½

File SSIS File Connection defines the columns as

 Guid: string [DT_STR] 36 
 Sql: text stream [DT_TEXT]

I am transferring it to the following SQL Server target table:

CREATE TABLE [dbo].[CodeObjectSql](
    [Guid] [char](36) NOT NULL,
    [Sql] [varchar](max) NOT NULL
) ON [PRIMARY]

When I preview the file, the column delimiter character appears as the last (37th) character of the guid first column and the row delimiter character appears as the last character of the SQL column value.

This is the error I get:

Error: 0xC02020A1 at Load CodeObjectSql, CodeObjectSql File [1]: Data conversion failed. The data conversion for column "Guid" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Load CodeObjectSql, CodeObjectSql File [1]: The "output column "Guid" (10)" failed because truncation occurred, and the truncation row disposition on "output column "Guid" (10)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Load CodeObjectSql, CodeObjectSql File [1]: An error occurred while processing file "C:\CodeObjectSql.csv" on data row 2.
Error: 0xC0047038 at Load CodeObjectSql, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "CodeObjectSql File" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

I then tried to change the file to Unicode and modified the column types to their unicode equivalents

 Guid: string [DT_WSTR] 36 
 Sql: text stream [DT_NTEXT]

and still no luck.

My experience is that SSIS can't handle occurrences of a row or col delimiter character in the data by using a text qualifier character and doubling a special character in a text value when it occurs to represent one occurrence in the data.

If my assumptions are all true based on my testing, what is the best format to use for this type data?

Try creating this table and pasting the data into an input file and see for yourself. :-)

sql-server-2008

ssis

asked on Stack Overflow Jun 22, 2013 by

Chad • edited Jun 22, 2013 by

Chad

2 Answers

Test File: All on one line.

Guid¼Sql½3afc912b-917d-4719-8ded-22e5d95930a3¼SELECT * FROM TABLE½a867fa30-f2c7-459e-8985-9ef42616991e¼SELECT * FROM TABLE½

Flat File Source Editor configuration:

Code Page: 1252
Format: Delimited
Text Qualifier: <none>
Header row delimiter:{CR/LF}
Header Rows to skip: 0
Column names in the first data row: Checked

Now go to Columns:

Row delimiter: 1/2
Column Delimiter: 1/4

Question/Suggestion: Can you not simplify you delimiters? Using 1/2 and 1/4 is unusual.

Also: Right-click on the data source. Go to Input and Output properties tab // Output columns // Guid. Change field properties to DT-STR (36).

answered on Stack Overflow Jun 24, 2013 by

Anoop Verma • edited Jun 24, 2013 by

Anoop Verma

Control Flow

Code for script task:

public void Main()
    {
        FileStream fs1 = new FileStream(@"C:\Temp\half.txt", FileMode.Open, FileAccess.Read);
        FileStream fs2 = new FileStream(@"c:\Temp\AllOnOne.txt", FileMode.Create);

        BinaryReader r = new BinaryReader(fs1);
        BinaryWriter w = new BinaryWriter(fs2);

        // Read data 
        for (int i = 0; i < fs1.Length; i++)
        {
            byte b = r.ReadByte();

            if (!b.Equals(Convert.ToByte('\n')) && !b.Equals(Convert.ToByte('\r')))
            {
                w.Write(b);
            }
        }

        w.Close();
        r.Close();

        fs2.Close();
        fs1.Close();           

        Dts.TaskResult = (int)ScriptResults.Success;
    }

DFT -- Use the settings as described in the previous answer from me.

Contents of half.txt is exactly what you have mentioned in your question. Please let us know it worked for you. If you found another solution, please post it.

answered on Stack Overflow Jun 26, 2013 by

Anoop Verma

User contributions licensed under CC BY-SA 3.0