December 15, 2014 at 9:40 am
I have an SSIS package authored in SSDT for VS 2013 that cancels itself immediately after validation completes and execution commences. This behavior occurs when executed either in VS 2013 or from within SQL Server. No error messages are thrown in either the debug window or the log output (log is capturing everything). The only thing that occurs differently on this package as compared to another package I am able to execute successfully is that a command line window briefly flashes when the package cancels itself—but it is gone so fast I cannot read it. The last several lines of the debug output are as follows:
-----------------
Information: 0x40043006 at Merge Info, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Merge Info, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Merge Info, All Users CSV [2]: The processing of file "C:\...\AllUsers.csv" has started.
Information: 0x400490F4 at Merge Info, Lookup Org [47]: Lookup Org has cached 957 rows.
Information: 0x400490F5 at Merge Info, Lookup Org [47]: Lookup Org has cached a total of 26719 rows.
Information: 0x40209340 at Merge Info, Lookup Org [47]: The number of unique rows added to the cache is 26719.
Information: 0x402090E2 at Merge Info, Lookup Org [47]: The Lookup Org processed 26719 rows in the cache. The processing time was 2.761 seconds. The cache used 33345312 bytes of memory.
Information: 0x402090DC at Merge Info, Opt Outs CSV [111]: The processing of file "C:\...\optouts-20141119.csv" has started.
Information: 0x402090DC at Merge Info, Output CSV [128]: The processing of file "C:\...\Output.csv" has started.
Information: 0x4004300C at Merge Info, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Merge Info, Opt Outs CSV [111]: The total number of data rows processed for file "C:\...\optouts-20141119.csv" is 3640.
SSIS package "C:\...\Opt_Outs_Merge.dtsx" finished: Canceled.
-----------------
Does anyone have any idea under what circumstances an SSIS package would cancel itself without throwing any errors?
December 15, 2014 at 10:37 am
December 15, 2014 at 4:07 pm
Thanks Eirikur; I gave it a look but eventually found a more particular solution to this issue I was experiencing—which I have documented below.
Long story short, it appears that the issue was in the configuration of the flat file connection manager. After a manual review of the source CSV file I found stray double quotes that were not being used as qualifiers and were not escaped. I decided to revert the text qualifier field on the connection manager to <none> which did allow the package to complete execution, though it appears I have some text cleanup to do. I still don't understand why an error was never thrown during execution as is typically done when a CSV can not be properly read, but if anyone else ever runs into this, perhaps this might prove helpful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply