Package with File System Task doesn't work without sensitive data with user keys

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.Could the security permissions be different on your packages that are failing to be moved from those that are succeeding?|||No, they're all set to EncryptSensitiveWithUserKey.

However, all of the packages involved have their sensitive information set to obtaining them from Indirect Configuration Files. The only way around that I found so far was to put in a dummy connection object, set up an XML file package configuration for that dummy, and deploy it that way.

I'm suspecting that the user key isn't generated at all when all the packages get their configs from Indirect Configuration Files. For some reason, DontSaveSensitive doesn't work either. My original package insists on the presence of a key. Might have something to do with file read/write/modify permissions that come with the key?

I still want to do away with the dummy though.

Package randomly stops

I have a very weird issue in my latest package. I run it and it just randomly stops. When I watch it in debug mode or not in debugging a command prompt window will flash for an instant (too fast to read) and then the package will stop. It stops inside of a for each loop and the "on completion" is never fired for the loop. I never receive any errors - its just like someone hit the stop button. Sometimes it will process hundreds of files before stopping, other times only a few. (And its not stopping on the same file each time.. it doesn't appear to be related to the file at all)

Any ideas what could be going on? How to troubleshoot?

The process might have crashed (the console window is SqlDumper collecting the crash information). Check if C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\ contains any dumps.
Also see this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541734&SiteID=1
Make sure you install the SP1, and if the problem still occurs, contact product support services.|||

Yes that folder contains files from the times it stopped - what can I read them with?

That thread mentions turning on error reporting - do you do that on the server or my dev workstation? (and where do I find that setting?)

Another problem I have (maybe related?) is that about once a day visual studio will just "disappear" while I am working on a package .. (or sometimes I am not even working on it) however there are no files for those occurances.

Yeah, I've been using SP1 for quite some time.

Thanks
Chris

|||

Chris Honcoop wrote:

Yes that folder contains files from the times it stopped - what can I read them with?

That thread mentions turning on error reporting - do you do that on the server or my dev workstation? (and where do I find that setting?)

The files are minidumps that can be read by debuggers, like Visual Studio. I would not recommend you to try to read them - they are meant for product devepers, not for users (unless you've built a custom task or transform and suspect it might be involved in the crash).

Use SQL Server Error and Usage Reporting tool to enable "Send error reports..." option - this will allow development team and product support to analize the problem and likely provide a fix in next SP. If you need solution sooner, contact Product Support Services.

|||The only custom objects I have installed is the RegEx one available from microsoft download - however I am not using it in this package (as it wouldn't work unless I was running the package from my workstation). I did uninstall it - however its still in my list of available objects if I go into "choose items..."|||

Well I ran the package directly on the server and it crashed as well so it doesn't appear to be something specific to my machine.

Overview of the dataflow component that fails is I have a flat file that has data in the header I need to keep and tack on each row. (see my thread on cross join) as well as one set of meta data that determines which rows in the file I need to keep. So I take the file and split it four ways - one of the split is basically all data except the metadata. I join one set of metadata to the main data to retrieve the rows of data I need. Then I also add a column to each datastream and hardcode it to 1 and join all the metadata back to the main datastream (since there is no cross join function).

Funny thing is I have been watching what it looks like when it crashes and the file and the split always work but the sorting before joining back into the merges or the merges themselves is where it seems to die (if indeed the graphical view at the time of crash is correct).

How do I know if it is sending the crash info to msft? Its crashed 5 times since turning that on and only once did it bring up a box saying visual studio encountered an error.

|||

Tried some more things as well... still random crashes: I've remade the package from scratch, restructured the dataflows 4 or 5 different way.. still random "dissappearing act" and memory dump to that location.

|||Please contact product support services - they'll be able to analize the crash dumps and provide a solution for the problem, or temporary workaround until the full solution is developed.

It is hardly possible to analize crashes via forum.

Overflow the disk I/O buffer

Hello,

I am getting "overflow the disk I/O buffer" in my SSIS, and what's weird is that when I construct the same SSIS in a new package, it works perfectly. I almost want to believe that it could be a bug. Some days when I import the files, it works fine, but some days it errors out with this error on the last column. Is there some setting with CR/LF or LF that I have to pay attention to avoid this type of random error?

Thanks for your help!

-Lawrence

Can you please provide the exact error message and any other error messages around it?

Thanks,
Phil|||

[Flat File Source [439]] Error: The column data for column "Avg Position" overflowed the disk I/O buffer.

[Flat File Source [439]] Error: An error occurred while skipping data rows.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (439) returned error code 0xC0202091.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.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: 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.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

|||What is the data type for the column, "Avg Position" and how many bytes is each row in the file?|||

Has there been a resolution to this? I have a similar problem occurring.

|||

Joshua Kasper wrote:

Has there been a resolution to this? I have a similar problem occurring.

No, because SQLChamp needs to respond to my last questions.|||

Column in database is float.

Bytes from the text file range from 1 to 35.6

-Lawrence

|||

Joshua Kasper wrote:

Has there been a resolution to this? I have a similar problem occurring.

And do you have a similar problem, or the exact same problem? Do you have the same error messages?|||

SQLChamp wrote:

Column in database is float.

Bytes from the text file range from 1 to 35.6

-Lawrence

What is the data type of the column inside SSIS? Make sure that it is what you expect it to be in all stages of the data flow.|||

I doubled check the Data Flow, and they are all float or mapping to the same column name.

Maybe the format of the data in the text file is causing SSIS to fail? Would you know what kind of data in the text file would do that? But I would assume the error log should indicate which row it error out on if it were format issue.

Any other places to check? or would this be random SSIS bug?

-Lawrence

|||

SQLChamp wrote:

I doubled check the Data Flow, and they are all float or mapping to the same column name.

Maybe the format of the data in the text file is causing SSIS to fail? Would you know what kind of data in the text file would do that? But I would assume the error log should indicate which row it error out on if it were format issue.

Any other places to check? or would this be random SSIS bug?

-Lawrence

Well, there isn't a float data type inside a data flow... Can you tell me what SSIS data type you have this column listed as?

Also, if you have a numeric field defined as numeric(4,2) and you have a number such as 1.234, it will fail, strictly speaking. 12.34 works. 123.4 does not.|||

FlatFile Metadata for [Avg Position] = DT_R4 (precision=0, length=0)

Float cannot set precision like numeric, so hopefuly precision is not the cause of this SSIS error.

What's weird is that ther are many other columns with Float, and they don't appear to cause any error, but [Avg Position] is at the last column, so not sure if that has an effect.

I am trying to attach the screenshot in this forum, but I am not sure you are able to see it below. Maybe this forum don't support screenshot.

-Lawrence

|||Since it's the last field in the file, it could be a row terminator issue.

How do you have the flat file setup? Ragged right? CSV?|||

In SSIS, [Avg Position] ColumnDelimiter is set to {CR}-{LF}

Row Delimiter - {CR}-{LF}

How do I check whether my text file source is compatible with the setting above?

-Lawrence

|||

I think the issue is that there is an extra blank line at the end. This does not happen all the time, so have to accommodate both (end of line & extra line). How would I setup SSIS to accommodate the extra blank line?

Thanks,

-Lawrence

Overflow the disk I/O buffer

Hello,

I am getting "overflow the disk I/O buffer" in my SSIS, and what's weird is that when I construct the same SSIS in a new package, it works perfectly. I almost want to believe that it could be a bug. Some days when I import the files, it works fine, but some days it errors out with this error on the last column. Is there some setting with CR/LF or LF that I have to pay attention to avoid this type of random error?

Thanks for your help!

-Lawrence

Can you please provide the exact error message and any other error messages around it?

Thanks,
Phil|||

[Flat File Source [439]] Error: The column data for column "Avg Position" overflowed the disk I/O buffer.

[Flat File Source [439]] Error: An error occurred while skipping data rows.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (439) returned error code 0xC0202091.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.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: 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.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

|||What is the data type for the column, "Avg Position" and how many bytes is each row in the file?|||

Has there been a resolution to this? I have a similar problem occurring.

|||

Joshua Kasper wrote:

Has there been a resolution to this? I have a similar problem occurring.

No, because SQLChamp needs to respond to my last questions.|||

Column in database is float.

Bytes from the text file range from 1 to 35.6

-Lawrence

|||

Joshua Kasper wrote:

Has there been a resolution to this? I have a similar problem occurring.

And do you have a similar problem, or the exact same problem? Do you have the same error messages?|||

SQLChamp wrote:

Column in database is float.

Bytes from the text file range from 1 to 35.6

-Lawrence

What is the data type of the column inside SSIS? Make sure that it is what you expect it to be in all stages of the data flow.|||

I doubled check the Data Flow, and they are all float or mapping to the same column name.

Maybe the format of the data in the text file is causing SSIS to fail? Would you know what kind of data in the text file would do that? But I would assume the error log should indicate which row it error out on if it were format issue.

Any other places to check? or would this be random SSIS bug?

-Lawrence

|||

SQLChamp wrote:

I doubled check the Data Flow, and they are all float or mapping to the same column name.

Maybe the format of the data in the text file is causing SSIS to fail? Would you know what kind of data in the text file would do that? But I would assume the error log should indicate which row it error out on if it were format issue.

Any other places to check? or would this be random SSIS bug?

-Lawrence

Well, there isn't a float data type inside a data flow... Can you tell me what SSIS data type you have this column listed as?

Also, if you have a numeric field defined as numeric(4,2) and you have a number such as 1.234, it will fail, strictly speaking. 12.34 works. 123.4 does not.|||

FlatFile Metadata for [Avg Position] = DT_R4 (precision=0, length=0)

Float cannot set precision like numeric, so hopefuly precision is not the cause of this SSIS error.

What's weird is that ther are many other columns with Float, and they don't appear to cause any error, but [Avg Position] is at the last column, so not sure if that has an effect.

I am trying to attach the screenshot in this forum, but I am not sure you are able to see it below. Maybe this forum don't support screenshot.

-Lawrence

|||Since it's the last field in the file, it could be a row terminator issue.

How do you have the flat file setup? Ragged right? CSV?|||

In SSIS, [Avg Position] ColumnDelimiter is set to {CR}-{LF}

Row Delimiter - {CR}-{LF}

How do I check whether my text file source is compatible with the setting above?

-Lawrence

|||

I think the issue is that there is an extra blank line at the end. This does not happen all the time, so have to accommodate both (end of line & extra line). How would I setup SSIS to accommodate the extra blank line?

Thanks,

-Lawrence