July 1, 2011 at 8:42 am
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.
July 1, 2011 at 9:12 am
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.
July 1, 2011 at 10:06 am
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)
July 1, 2011 at 10:11 am
What happens when you change your data source's SELECT to put double quotes around that field?
SELECT '"' + [FreeTextColumn] + '"' AS QuotedFreeTextColumn
July 1, 2011 at 10:20 am
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
------------------------------
July 1, 2011 at 11:11 am
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 QuotedFreeTextColumnI 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.
July 1, 2011 at 11:26 am
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
July 1, 2011 at 11:34 am
laddu4700 (7/1/2011)
I have corrected the metadata in data source and ran the package, still getting errorPackage 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