December 3, 2008 at 2:27 pm
I have two similar SSIS jobs, one that runs on a daily basis, one on a monthly basis. Both are exactly the same, except the daily picks up the 'daily' files and puts them into the 'daily' tables, the monthly picks up the EOM files and puts them into a 'monthly' table.
The only diff between the tables is that the monthly table has a 'control id' (int) which is appended via a 'derived column' and added to the data flow.
The monthly and daily files are exactly the same layout, and both are brought in via data flow tasks.
The structure of the files is:
first row is a header consisting of the name of the file, and the date, 2nd and subsequent rows are tab delimited.
What appears to be happening is that even though I am saying in the connection "ignore first header row", in the monthly file, an extraneous 0 is appearing. This "0" is NOT in the original file (have even looked at it in hex to be sure).
Sticking a data viewer in the mix after the file gets read, but before the derived column gets put on shows that there is a first row containing "0" (and nothing else).
Carrying out the same exercise on the daily SSIS package shows everything as expected (ie diff from the monthly) - and no errors.
Daily file:
ASSETCLASSES 02/12/2008
CASH Cash 001 Y
EQUITY Equities 004 Y
ALTERNAT Alternative Investments 005 Y
PROPERTY Property 003 Y
FIXEDINT Fixed Interest 002 Y
Monthly File:
ASSETCLASSES 30/11/2008
EQUITY Equities 004 Y
PROPERTY Property 003 Y
CASH Cash 001 Y
FIXEDINT Fixed Interest 002 Y
ALTERNAT Alternative Investments 005 Y
In the data viewer, we have:
asset_code asset_descr sort_seq print_detail
0
EQUITY Equities 4 Y
PROPERTY Property 3 Y
CASH Cash 1 Y
FIXEDINT Fixed Interest 2 Y
ALTERNAT Alternative Investments 5 Y
then, after the derived column is added, we get:
asset_code asset_descr sort_seq print_detail ctl_id
0 101
EQUITY Equities 4 Y 101
PROPERTY Property 3 Y 101
CASH Cash 1 Y 101
FIXEDINT Fixed Interest 2 Y 101
ALTERNAT Alternative Investments 5 Y 101
I've done the following:
Copied the daily (working) file to the eom file and run that - failed (same error as before).
Deleted the flat file connection manager, deleted the entire data flow task, saved, cleaned, rebuilt, saved, closed BIDS, reopened BIDS, recreated FFCM, recreated the data flow task, saved, cleaned, rebuilt, saved, reran job - failed.
I did note that even after deleting the ffcm and the data flow task, that I had 15 errors all saying something like "can't find cmAssData" which is the FFCM for the file.
I've even tried a 'conditional split' to remove any row that has the sequence number of 0 - task failed on reading the flat file.
Also, if I mark it to ignore errors, I end up with (tab)(tab)0,(tab),101 as the first line in the table.
If I do not 'ignore', I get the following errors:
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "print_detail" 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 Source [1]] Error: The "output column "print_detail" (53)" failed because truncation occurred, and the truncation row disposition on "output column "print_detail" (53)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [1]] Error: An error occurred while processing file "\\MyServerName\SomeDirectory\File Transfers\assdata_eom.tab" on data row 1.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (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.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
December 3, 2008 at 4:46 pm
Just tried pointing the daily package at the monthly file, and that worked fine. Its obviously something to do with the flat file reader, but even after deleting the connection manager AND the data flow task, saving, cleaning, rebuilding, shutting BIDS down, restarting, rebuilding the connection manager and data flow tasks, it STILL doesn't work.
*frustrated*
December 3, 2008 at 7:59 pm
Found the answer...
When I deployed it to my test server, I enabled package configurations. When I brought it back into Dev and changed the UNC path for the file(s) to a variable + filename, I left the config file alone - not realising that it would actually make a difference.
By switching package configurations off, then deleting and recreating the connection managers and reconfiguring the data flow tasks, the problem appears to go away.
Q: for Microsoft (if anyone's watching). Why doesn't "clean" and "rebuild" replace the config file value with the new CM value (even if they are exactly the same)? Is this a bug?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply