SSIS package execution error

  • Hi,

    I am trying export data from SQL Server 2008 table to csv file(unixformat).

    I created a SSIS package for this task. Added SQL Server table as source and flatfile destination(csv). But the package is failing. It exporting only 14003 rows out of 25257 rows. There is a free text column in table, is this something issue with that column data?

    Below are the details:

    Table Name : EntryAnswers

    Column_nameTypeLength

    EntryAnswersPatientIDbigint8

    EntryAnswersIDint4

    EntrySectionTypesmallint2

    EntryIDint4

    EntryQuestionIDint4

    EntryAnswerIDint4

    EntryAnswersFreeTextnvarchar6000

    EntryAnswersParentIDint4

    EntryAnswersParentPatientIDbigint8

    EntryAnswersVisiblebit1

    EntryAnswersCreatedDatedatetime8

    EntryAnswersModifiedDatedatetime8

    EntryAnswersCreatedBybigint8

    EntryAnswersModifiedBybigint8

    EntryPatientIDbigint8

    Here is my source table sql command

    SELECT [EntryAnswersPatientID]

    ,[EntryAnswersID]

    ,[EntrySectionType]

    ,[EntryID]

    ,[EntryQuestionID]

    ,[EntryAnswerID]

    ,Replace (ISNULL (EntryAnswersFreeText, ''), ',' , '') as EntryAnswersFreeText

    ,CASE WHEN EntryAnswersParentID IS NULL THEN '' ELSE CONVERT(VARCHAR, EntryAnswersParentID) END AS EntryAnswersParentID

    ,CASE WHEN EntryAnswersParentPatientID IS NULL THEN '' ELSE CONVERT(VARCHAR, EntryAnswersParentPatientID) END AS EntryAnswersParentPatientID

    ,[EntryAnswersVisible]

    ,CONVERT(varchar(10), EntryAnswersCreatedDate, 110) + SUBSTRING(CONVERT(varchar, EntryAnswersCreatedDate,

    109), 12, 9) + SUBSTRING(CONVERT(varchar, EntryAnswersCreatedDate, 109),25,2) as EntryAnswersCreatedDate

    ,CONVERT(varchar(10), EntryAnswersModifiedDate, 110) + SUBSTRING(CONVERT(varchar, EntryAnswersModifiedDate,

    109), 12, 9) + SUBSTRING(CONVERT(varchar, EntryAnswersModifiedDate, 109),25,2) as EntryAnswersModifiedDate

    ,[EntryAnswersCreatedBy]

    ,[EntryAnswersModifiedBy]

    ,[EntryPatientID]

    FROM [MyDMVNSNY].[dbo].[EntryAnswers]

    GO

    Execution Results:

    Package Package

    Validation has started

    Task Data Flow Task

    Validation has started

    [SSIS.Pipeline] Information: Validation phase is beginning.

    Progress: Validating - 0 percent complete

    Progress: Validating - 50 percent complete

    Progress: Validating - 100 percent complete

    Validation is completed

    Start, 10:20:50 AM

    [SSIS.Pipeline] Information: Validation phase is beginning.

    Progress: Validating - 0 percent complete

    Progress: Validating - 50 percent complete

    Progress: Validating - 100 percent complete

    [SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

    [SSIS.Pipeline] Information: Prepare for Execute phase is beginning.

    Progress: Prepare for Execute - 0 percent complete

    Progress: Prepare for Execute - 50 percent complete

    Progress: Prepare for Execute - 100 percent complete

    [SSIS.Pipeline] Information: Pre-Execute phase is beginning.

    Progress: Pre-Execute - 0 percent complete

    Progress: Pre-Execute - 50 percent complete

    [Flat File Destination [16]] Information: The processing of file "E:\EntryAnswers.csv" has started.

    Progress: Pre-Execute - 100 percent complete

    [SSIS.Pipeline] Information: Execute phase is beginning.

    [Flat File Destination [16]] Error: Data conversion failed. The data conversion for column "EntryAnswersFreeText" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    [Flat File Destination [16]] Error: Cannot copy or convert flat file data for column "EntryAnswersFreeText".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Flat File Destination" (16) failed with error code 0xC02020A0 while processing input "Flat File Destination Input" (17). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    [OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.

    [SSIS.Pipeline] Information: Post Execute phase is beginning.

    Progress: Post Execute - 0 percent complete

    Progress: Post Execute - 50 percent complete

    [Flat File Destination [16]] Information: The processing of file "E:\EntryAnswers.csv" has ended.

    Progress: Post Execute - 100 percent complete

    [SSIS.Pipeline] Information: "component "Flat File Destination" (16)" wrote 13250 rows.

    [SSIS.Pipeline] Information: Cleanup phase is beginning.

    Progress: Cleanup - 0 percent complete

    Progress: Cleanup - 50 percent complete

    Progress: Cleanup - 100 percent complete

    Task Data Flow Task failed

    Finished, 10:20:52 AM, Elapsed time: 00:00:02.449

    Validation is completed

    Start, 10:20:50 AM

    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Finished, 10:20:52 AM, Elapsed time: 00:00:02.714

    Thanks for your help.

  • laddu4700 (7/1/2011)


    There is a free text column in table, is this something issue with that column data?

    My first guess would be to look for delimiter characters inside the free text column. Or try something in your SQL like: SELECT '"' + [FreeTextColumn] + '"' AS QuotedFreeTextColumn

    Investigate that to see if that yields anything.

  • most of the rows have a "

    also have comma's in most of the rows.

    Is there any way in SSIS package to resolve this and export the data correctly into csv FILE(UNIX format)

  • What happens when you change your data source's SELECT to put double quotes around that field?

    SELECT '"' + [FreeTextColumn] + '"' AS QuotedFreeTextColumn

  • rgtft (7/1/2011)


    What happens when you change your data source's SELECT to put double quotes around that field?

    SELECT '"' + [FreeTextColumn] + '"' AS QuotedFreeTextColumn

    I am getting package validation error.

    Package validation error

    ------------------------------

    ADDITIONAL INFORMATION:

    Error at Data Flow Task [SSIS.Pipeline]: input column "EntryAnswersFreeText" (88) has lineage ID 40 that was not previously used in the Data Flow task.

    Error at Data Flow Task [SSIS.Pipeline]: "component "Flat File Destination" (16)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • laddu4700 (7/1/2011)


    rgtft (7/1/2011)


    What happens when you change your data source's SELECT to put double quotes around that field?

    SELECT '"' + [FreeTextColumn] + '"' AS QuotedFreeTextColumn

    I am getting package validation error.

    Package validation error

    That's okay and to be expected in SSIS. You changed the contents of the workflow upstream from other components, so their metadata needs to be corrected. Open the individual components downstream from your data source and fix the error.

  • I have corrected the metadata in data source and ran the package, still getting error

    Package execution results:

    ….

    [Flat File Destination [61]] Error: Data conversion failed. The data conversion for column "entryanswersfreetext" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    [Flat File Destination [61]] Error: Cannot copy or convert flat file data for column "entryanswersfreetext".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Flat File Destination" (61) failed with error code 0xC02020A0 while processing input "Flat File Destination Input" (62). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    [OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.

    [SSIS.Pipeline] Information: Post Execute phase is beginning.

    Progress: Post Execute - 0 percent complete

    Progress: Post Execute - 50 percent complete

    [Flat File Destination [61]] Information: The processing of file "C:\EntryAnswers.csv" has ended.

    Progress: Post Execute - 100 percent complete

    [SSIS.Pipeline] Information: "component "Flat File Destination" (61)" wrote 13250 rows.

    [SSIS.Pipeline] Information: Cleanup phase is beginning.

    Progress: Cleanup - 0 percent complete

    Progress: Cleanup - 50 percent complete

    Progress: Cleanup - 100 percent complete

    Task Data Flow Task failed

    Finished, 1:20:42 PM, Elapsed time: 00:00:00.265

    Validation is completed

    Start, 1:20:41 PM

    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Finished, 1:20:42 PM, Elapsed time: 00:00:00.265

  • laddu4700 (7/1/2011)


    I have corrected the metadata in data source and ran the package, still getting error

    Package execution results:

    ….

    [Flat File Destination [61]] Error: Data conversion failed. The data conversion for column "entryanswersfreetext" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    From the error it's either truncation (field too long for destination) or bad character. Try purposely shortening this field (e.g LEFT(field, 20)) in your SQL, refix the metadata errors and see if you still get the error.

    Also, you could try sorting the data source (ORDER BY), put a data view in the workflow, and take a look at what seems to be the offending record.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply