Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked |||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked |||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

fail component error notification

I have created a package which forms data from flat file to sql database.I have a data flow where all my data is processed to destination.if the package fails by any chance i want an email notification with the cause of the error.how can i do this.please let me know..I'd start with Send Mail task and the OnError event handler for the package.|||Will i be able to send mail with the list of errors occured if the do the above|||Can you specify your goal in more detail? Do you want one email with all errors listed? Do you want all errors, or just the primary error that caused the package to fail?|||I want all the errors in one email.|||

This post might help:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

I created an example that uses a Script task in the OnError event to collect all the errors, and then uses a second Script task in the OnPostExecute event to process them. You could simply add a Send Mail task to the OnPostExecute to send the email.

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
...
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
...
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael Powell
Parsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegr oups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>
|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?
|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegro ups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>
|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?
|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)
|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx
|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegr oups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>
|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegro ups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
..
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
..
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael PowellParsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegroups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegroups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegroups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegroups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
...
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
...
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael PowellParsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegroups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegroups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegroups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegroups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>