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

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

After getting an RPC error when trying to login to my server via either consol or Terminal services, then rebooting, I can no longer connect to SSIS

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "BG-22SQL" failed:

The service did not respond to the start or control request in a timely fashion.

.

Connect to SSIS Service on machine "BG-22SQL" failed:

The service did not respond to the start or control request in a timely fashion.

.

Can you start the SSIS service on this machine (try starting it via the Service Control Manager)?

If the service fails to start, please see if this KB article applies to your configuration:
http://support.microsoft.com/kb/918644sql

Failed to notify 'Operator' via email

32-bit SQL 2005 sp2 jobs recieve this error when trying to send an email to an Operator. Database Mail is setup and is working for all SSIS jobs where the email is sent through code or Send email task but simply trying to notify on job completion will not work even though the Agent is running & configured to use Database Mail profile with proper permissions.

Make sure sql agent configured properly and restart sql agent after mail configuration and test the mail from agent..

Make sure operator email is correct...and check if there are any other errors in mail log.

|||Enable DatabaseMail on the SQL Server Agent -> properties -> Alert System (check the checkbox), and then restart SQL Server Agent.

Failed to decrypt XML node "DTS:Password"

Hi!
I made an SSIS package and it runs fine on my workstation, but it fails when I run it on the SQL Server.
I'm using an ODBC connection and when I run the package, I get the error message:
"Login failed. Check for valid user ID, server name or password".
I tried to load the project in Visual Studio on the Server and got the error message above:
Failed to decrypt XML node "DTS:Password"
How can I set up the permissions for decrypting the password?

Thanks for any help!

The problem is that you have the ProtectionLevel set to "EncryptSensitiveWithUserKey". This means the package can be executed only by the user that created the package and on the machine that it was created. (unless you use a domain account and use machines in the same domain)

If you import the package on your server I recomend to change the ProtectionLevel to "EncryptSensitiveWithPassword" - will ecnrypt sensitive information like passwords - or if you store the package on SqlServer you can use "Rely on server storageand rolse for access control" - this means that the package will be stored unencrypted on the SQL Server.

The package can be stored unencrypted ONLY in the SQL Server.

HTH.
Ovidiu Burlacu

|||Thanks for the explanation!
I tried to use the last option"Rely on server storageand rolse for access control", however it causes a lot of error messages in the Business Intelligence Studio and I can not create a build with a deployment manifest. The help sais, this option is not accessible from Visual Studio, but I can not figure out, how to use it.
Can you tell me, how I can use it?
|||

Are you saving the package on a SQL Server or to a file? You can use ServerStorage ONLY if you save the package on SQL Server

To save a package from BIDS directly to SQL Server use File-> "Save Copy of <package_name>.dtsx As..." and in the dialogs that appears you can specify a SQL Server

If you want to create a deployment manifest encrypt with a passord and when you run the Deployment wizard and specify the destination to be a SQL Server the you can choose the "Rely on server storage and role for access control".

HTH,
Ovidiu

|||Thanks alot!

Failed to call SSIS package from ASPX C# Web Page

Hi, Everyone:

I am getting the following error message when I try to execute a SSIS package from an asp.net page written in C# 2.0. What I am trying to do is basically just click on a button in the web page and it will execute the package. The code to execute the package is pretty simple. I pass the path of the DTS package stored in the local folder. This works fine in the machine where SQL2005 is installed locally. But it fail when I have a seperate Web Server and SQL Server. Any ideas? Do I have to install SSIS or SQL2005 on a web server as well?

Thanks

static public string Execute_SSIS_DTS(string DTS_Path)

{

Microsoft.SqlServer.Dts.Runtime.Application app;

app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package package = app.LoadPackage(DTS_Path, null);

DTSExecResult result = package.Execute();

return result.ToString();

}

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Line 226: static public string Execute_SSIS_DTS(string DTS_Path)

Line 227: {

Line 228: Application app = new Application();

Line 229: Package package = app.LoadPackage(DTS_Path, null);

Line 230: DTSExecResult result = package.Execute();

[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +43

[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +169
Utilities.Execute_SSIS_DTS(String DTS_Path) in c:\Inetpub\wwwroot\MasterTables\App_Code\Utilities.cs:228
MasterTables_Admin_MasterTables_LOINC_External.btn_SyncLISTest_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\MasterTables\MasterTables_Custom\MasterTablesCustom_LOINC_External.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +141
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3215

Yes, to use SSIS object model and to execute package you do need to install SSIS on this machine, of course.

See my blog entry for some more details
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

Failed to acquire connection tempdb

Hi, I have an SSIS project, has a connection to tempdb (local sql2k5 server), 3 tasks, creating a table, populating the table, then deleting the table. of course when I test the connection i GET TEST Succesfull (I amusing sa ...)

Whenever I run the package, all tasks become red colored indicating, I get the following error :

[Execute SQL Task] Error: Failed to acquire connection "tempdb".
connection may not be configured correctly or you may not have the right permissions on this connection.vance
-
Any help would be appreciated. Thanks in advance

See if it complains also about sa's password when package executes.

fail connect to intergrate services

Message:

Cannot connect to server
additional information
- failed to retrieve data for this request.(microsoft.sqlserver.smoenum)
- connect to SSIS service on machine "server" failed:
error loading type library/dll
- connect to SSIS service on machine 'server' failed :
error loading type library/dll

any idea?

soonyu

Have you started the SSIS Service?

-Jamie

|||

if you talking about services->SQL Server Integration Services
then confirm service already start.

an still the same Problem
-soonyu

|||using sql2005 64bit
solve with install service pack 1

--soonyu

Extremely long lines

Hi,

When i open a project in ssis show the message:

"Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file."

I click Yes and my project open normally. Someone know why this happen? My project is small, have one package with any imports excel files to Sql Server 2005.

Thanks

Andr Rentes

Hi

Have a look at this link from the forum. Hope it helps:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=313598&SiteID=1

Cheers,

Grant

Extracting XML data from SQL 2000 to SQL 2005 database table.

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

VDeevi wrote:

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

What methods have you tried so far and why do they fail?

-Jamie|||Hi Jamie,

Thanks for your reply, We tried with OPENXML, sp_xml_prepareddocument etc. but they are all round about things we want to know is there any simple technique in SSIS in order to retrieve the data from XML stored in SQL Server 2000 TEXT field.

Thanks,|||I've just had a go at this...

When you select the data a TEXT field appears in the SSIS pipeline as a DT_TEXT. To insert it into a XML field use a derived column transform to convert it to DT_NTEXT.
If you don't like DT_NTEXTs in your pipeline then convert it to DT_WSTR and then to DT_STR which can also be inserted into an XML field.

So yes, SSIS can do this very very well.

-Jamie

Extracting package information from SSIS

Hi all,

I am supposed to maintain a bunch of excels documenting all the mappings I'm doing in SSIS. The excels have the following format:

Target Field | Target Type | Source Table | Source Field | Source Type | Transformation Rule

Apart from being incredibly tedious, it is hard to keep every excel current, as there are other people adding and taking information from the database model.

I plan to extract this information from SSIS and create the excels dinamically, but, apart from parsing the .dtsx for each package, I see no other way of getting what I need.

Any suggestions on how I should do this? Is there an easier way?

Cheers,

David LealThe SSIS object model provides access to all aspects of the package, you can use it from both managed and unmanaged code. Also, take a look as SQL BI Metadata Samples that has similar goal of documenting the package, you may find some of its code useful for your project.|||Thank you very much, Michael. I'll look into that.

Extracting Contents of Object Variable in script Task

Hey Guys,

Im pretty new to Scripting in SSIS, but i have worked a fair bit out already, although i am stuck with the following problem.. If anyone can help out that would be great!!

Ok, i have a package that loops through records in a table, and extracts the detailed lines in a For Each loop. and stores them in a variable called DetailRecordSet with an data type of object.

I have a built a script task so that i can send out an email confirmation for each order, and i want to list the details in a HTML Table in the body of the message ( I have read that the standard send mail task wont support this, but found an example of using .NET code to generate the email message which will support it) .

What i want to know is how do i reference the columns in the Object variable so i can extract each line and add it into my string that i am creating with HTML codes.. I

e:( str = "<TABLE> <TR>" + Cstr(Variable for 1st column in recordset) + " </TR>"

if any can help my, that would be most appriciated.. Ive tried all different things that ive found on the net, but nothing is working.

Thanks in Advance

Scotty

To know how to interact with SSIS object variable's underlying type, you need to know the type itself. That is, the type of the "thing" stored by reference in the SSIS object variable.

What the actual underlying type of the variable stored in the SSIS variable of type object?

System.Windows.Forms.MessageBox.Show(Dts.Variables("DetailRecordSet").Value.GetType().FullName)

The above MessageBox() will display the underlying type in your script task, excepting Com callable wrappers (of type System.__Object).
|||

Sorry for the late reply Jaegd. but have been away for a few days,,

This object type is System.Object. from the variable section i select the data type to be Object.

I appricate your help still, cause i am still stuck

Thanks

Scotty

|||

As per the previous reply, knowing what is in the object is crucial. From your reply it is a COM object, so I'll guess that it is probably an ADO recordset. IN which case take a look here for some links and pointers - http://blogs.conchango.com/jamiethomson/archive/2005/02/08/SSIS_3A00_-Accessing-a-recordset-in-a-script-task.aspx

If that does not help, then help me, as I don't understand how you got there. So what has populated the variable exactly, and what method was used? If it is the For Each loop, what enumerator have you used?