Home Forums SQL Server 2005 Administering SQLServer Agent 'sed' job reporting success; but does nothing RE: SQLServer Agent 'sed' job reporting success; but does nothing

  • Paul White

    SSC Guru

    Points: 150341

    I've had a much better idea. Create an XML format file like so:

    <?xml version="1.0" ?>

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <RECORD>

    <FIELD ID="01" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="02" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="03" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="04" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="05" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="06" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="07" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="08" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="09" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

    <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="50"/>

    </RECORD>

    <ROW>

    <COLUMN SOURCE="01" NAME="col1_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="02" NAME="col2_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="03" NAME="col3_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="04" NAME="col4_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="05" NAME="col5_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="06" NAME="col6_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="07" NAME="col7_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="08" NAME="col8_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="09" NAME="col9_dirty" xsi:type="SQLVARYCHAR" />

    <COLUMN SOURCE="10" NAME="col10_dirty" xsi:type="SQLVARYCHAR" />

    </ROW>

    </BCPFORMAT>

    And use the BULK OPENROWSET provider to allow you to pre-process the raw data before inserting into the destination table:

    INSERT dbo.zz_u_test_TEMP WITH (TABLOCK)

    (

    col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty,

    col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty

    )

    SELECT REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)),

    REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)),

    REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)),

    REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)),

    REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)),

    REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)),

    RowSource.col7_dirty,

    RowSource.col8_dirty,

    RowSource.col9_dirty,

    RowSource.col10_dirty

    FROM OPENROWSET

    (

    BULK 'C:\Documents and Settings\Paul\Test.data',

    FORMATFILE = 'C:\Documents and Settings\Paul\format.xml',

    CODEPAGE = 'RAW',

    FIRSTROW = 0,

    LASTROW = 0,

    MAXERRORS = 0,

    ROWS_PER_BATCH = 0

    ) RowSource;

    This preserves all the benefits of minimally-logged fast bulk load, while giving you the opportunity to apply transformations in the SELECT clause. Very cool.

    The data loaded using the simple example above is:

    col1_dirty col2_dirty col3_dirty col4_dirty col5_dirty col6_dirty col7_dirty col8_dirty col9_dirty col10_dirty

    1 7 D 2 _ NULL 05/25/2007 05/25/2007 10.00 2.15

    Paul