Error when using SP in data flow

I want to execute a stored procedure in an OLE DB source in a Data Flow Task.

The stored procedure has a parameter.

When I put in an SQL command as :

EXEC sp_readcustomers 1

(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.

The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :

EXEC sp_readcustomers ?

In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.

@.par_company_id has been declared as int, var_company_id as Int16

When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :

Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Preview : No value given for one or more required parameters (Microsoft SQL Native Client)

I have already installed SQL 2005 SP2 and VS2005 SP1.

I have tried everything I know, please help ?!?!

Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

|||

Ronald Dirkx wrote:

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.

ERROR WHEN I TRY TO EDIT A SCRIPT TASK

Today, I must to re-install VS2005 and SQL Server 2005 desktop tools in my computer, because I install SQL Server 2005 SP2 desktop tool for an special features that I need.

This caused that the edition of the Script Task gives to me the following error:

Cannot show the editor for this task

Additional information:

Engine returned Unknown Error (Microsoft.VisualBasic.Vsa.DT)

After the reinstalación, the error continues appearing, and I do not know that to do so that it works fine again.

Can any help me with this?

Thanks.

Alejandro

Are you on Vista?

http://blogs.msdn.com/mattm/archive/2007/03/09/sp2-vista-and-script-tasks.aspx|||No. I′m in XP SP2|||

Take a look at this thread:

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

sql

Error when I try to add Output Column in in Data Conversion

Here is what I get. Is this an install problem or is this how this software works?

===================================

Error at Data Flow Task [Data Conversion [720]]: An output cannot be added to the outputs collection.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.InsertOutput(DTSInsertPlacement eInsertPlacement, Int32 lOutputID)
at Microsoft.DataTransformationServices.Design.Controls.ComponentMetaDataTreeView.AddOutput()

What did you do to cause the error?

-Jamie

|||When in a package I added a data flow object then drilled into to add data source etc.

One of the items in the tool bar is Data Conversion. I whent to Advance edit on DataConversion on the last tab there was a button to "Add Output"

The result was the error above|||

PeterFreeb wrote:

When in a package I added a data flow object then drilled into to add data source etc.

One of the items in the tool bar is Data Conversion. I whent to Advance edit on DataConversion on the last tab there was a button to "Add Output"

The result was the error above

This is completely correct. it is not possible to add an output to the Data Conversion component. It only has one output and that exists as soon as you drag it onto the design surface.

What exactly are you attempting to do?

-Jamie

|||I have a unicode field in SQL and am trying to go non unicode or VARCHAR2 in Oracle.|||

PeterFreeb wrote:

I have a unicode field in SQL and am trying to go non unicode or VARCHAR2 in Oracle.

OK, well you have no need to go anywhere near the Advanced Editor. Just open the component up by double-clicking on it and add your data conversion in the GUI.

-Jamie

|||OK thanks I have a more involved question for Jamie or whoever knows...

I need to do an update or insert - I could do this easyly in SQL am not sure how to accomplish this in SSIS.

1. Check if the record is in the destination
2. If true -> Update
3. Else ->Insert|||

PeterFreeb wrote:

OK thanks I have a more involved question for Jamie or whoever knows...

I need to do an update or insert - I could do this easyly in SQL am not sure how to accomplish this in SSIS.

1. Check if the record is in the destination
2. If true -> Update
3. Else ->Insert

All documented here Peter:

Checking if a row exists and if it does, has it changed?
(http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx)

-Jamie

Error when clicking OK on edit of task

I've got a package that was imported from SQL 2000 into SQL 2005. I'm trying to edit it in Business Int. Dev Studio. When I open what used to be a data pump step in 2000, I'm able to edit items. However, when I click OK, it puts an error right below the buttons:

"" is not a value type

And it won't let me save my changes. I have no idea where this error is coming from. All the data pumps in this package cause the same error. Your help is appreciated.Found the solution thanks to our server support staff: They noticed that all the global variables (now referred to as inner variables) were missing their type. They had names and values, but no type. To give them a type, we just needed to change the name of the variable and then change it back -- this caused the type dropdown to become active. Then we had a type for each variable as well. I hope this helps others who run into this same issue.|||

arc_dev wrote:

Found the solution thanks to our server support staff: They noticed that all the global variables (now referred to as inner variables) were missing their type. They had names and values, but no type. To give them a type, we just needed to change the name of the variable and then change it back -- this caused the type dropdown to become active. Then we had a type for each variable as well. I hope this helps others who run into this same issue.

Well done for picking this up. Did you use the migration wizard? If so, this is a bug in the migration wizard. Could you report it at Microsoft Connect?

-Jamie

|||I just created a bug report with screen captures of what it does.

Error using FTP Task

I am using SQL Server 2005 - CTP June 2005 and I am trying to create a simple FTP task. Currently, my package consists of one FTP Task object in the Control Flow tab. I create a new FTP Connection Manager using <New Connection> in the FtpConnection property and verify the connection by selecting the "Test Connection" button. So far so good. However, when I return to the designer a red X appears in the FTP Task object with the following error:

"Error 1 Validation error. FTP Task FTP Task: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered. Package.dtsx"

If I execute the task as is, I get the following runtime error:

"Error at Package: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at FTP Task [FTP Task]: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered.
Error at FTP Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)"

When I edit the FTP Task, the FtpConnection is set properly. What am I doing wrong? Any help would be greatly appreciated. Thank you.

Clay Benoit

Hi Clay
This is a validation error telling you to complete the properties in the FTP dialog, either select send or the receive option with complete valid paths
for example of FTP please try the http://www.sqlis.com/

Thanks|||I get the same error:

Error: 0xC002918A at FTP Copy blahblah, FTP Task: Unable to receive files using "FTP Connection Manager".

Error: 0x80004005 at FTP Copy blahblah, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

I have a FTP component with wildcards on the files
(RemotePath: "/somewhere/myfile*.*")
OverwriteFileatDest : False
Local path is "Existing Folder" with proper local folder unc.

The errors come after the list of files that could not be copied because they already exist on the local machine.
any ideas?
Horseshoe

|||There are two problems:
It appears that your setup is corrupt because it cannot find the error messages file.

You're attempting to download files to a location where identically named files are stored yet you have OverwriteFileAtDest set to false. Move the files or set that property to true and the task should succeed.|||

This looks like a bug to me

The first error is valid - the file does not exist

Error: 0xC0029183 at FTP Delete XST from Remote, FTP Task: File represented by "User::XSTFile" does not exist.

Error: 0x80004005 at FTP Delete XST from Remote, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

The second looks like someone at MS fast fingered 29183 as 20918, and that message is not in the message files.

So you get two errors for the price of one...

Is there a way to make an expected error not increment the error count? Or do you just have to set the MaxErrors high enough to not have it matter?

|||

Here is what worked for me...

It turns out of course that the ftp site was unix. I knew the variable as /subdir/file.txt.gz but I could not get that file to download, much less the others I wanted to itterate. I tested localy using FileZilla FTP Server on a local windows box and it worked perfectly. It was not until I decided to NOT use my generated file location variable and to use a design time fixed variable that it became clear that a Unix path was needed. What looked to me in IE as /subdir/file was was really /export/home/subdir/subdir/file. Only after I specificaly picked the file I wanted with the FTP Task did the real path show up.

Hope this helps.

Error using FTP Task

I am using SQL Server 2005 - CTP June 2005 and I am trying to create a simple FTP task. Currently, my package consists of one FTP Task object in the Control Flow tab. I create a new FTP Connection Manager using <New Connection> in the FtpConnection property and verify the connection by selecting the "Test Connection" button. So far so good. However, when I return to the designer a red X appears in the FTP Task object with the following error:

"Error 1 Validation error. FTP Task FTP Task: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered. Package.dtsx"

If I execute the task as is, I get the following runtime error:

"Error at Package: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at FTP Task [FTP Task]: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered.
Error at FTP Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)"

When I edit the FTP Task, the FtpConnection is set properly. What am I doing wrong? Any help would be greatly appreciated. Thank you.

Clay Benoit

Hi Clay
This is a validation error telling you to complete the properties in the FTP dialog, either select send or the receive option with complete valid paths
for example of FTP please try the http://www.sqlis.com/

Thanks|||I get the same error:

Error: 0xC002918A at FTP Copy blahblah, FTP Task: Unable to receive files using "FTP Connection Manager".

Error: 0x80004005 at FTP Copy blahblah, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

I have a FTP component with wildcards on the files
(RemotePath: "/somewhere/myfile*.*")
OverwriteFileatDest : False
Local path is "Existing Folder" with proper local folder unc.

The errors come after the list of files that could not be copied because they already exist on the local machine.
any ideas?
Horseshoe

|||There are two problems:
It appears that your setup is corrupt because it cannot find the error messages file.

You're attempting to download files to a location where identically named files are stored yet you have OverwriteFileAtDest set to false. Move the files or set that property to true and the task should succeed.|||

This looks like a bug to me

The first error is valid - the file does not exist

Error: 0xC0029183 at FTP Delete XST from Remote, FTP Task: File represented by "User::XSTFile" does not exist.

Error: 0x80004005 at FTP Delete XST from Remote, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

The second looks like someone at MS fast fingered 29183 as 20918, and that message is not in the message files.

So you get two errors for the price of one...

Is there a way to make an expected error not increment the error count? Or do you just have to set the MaxErrors high enough to not have it matter?

|||

Here is what worked for me...

It turns out of course that the ftp site was unix. I knew the variable as /subdir/file.txt.gz but I could not get that file to download, much less the others I wanted to itterate. I tested localy using FileZilla FTP Server on a local windows box and it worked perfectly. It was not until I decided to NOT use my generated file location variable and to use a design time fixed variable that it became clear that a Unix path was needed. What looked to me in IE as /subdir/file was was really /export/home/subdir/subdir/file. Only after I specificaly picked the file I wanted with the FTP Task did the real path show up.

Hope this helps.

Error using FTP Task

I am using SQL Server 2005 - CTP June 2005 and I am trying to create a simple FTP task. Currently, my package consists of one FTP Task object in the Control Flow tab. I create a new FTP Connection Manager using <New Connection> in the FtpConnection property and verify the connection by selecting the "Test Connection" button. So far so good. However, when I return to the designer a red X appears in the FTP Task object with the following error:

"Error 1 Validation error. FTP Task FTP Task: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered. Package.dtsx"

If I execute the task as is, I get the following runtime error:

"Error at Package: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at FTP Task [FTP Task]: Attempt to read message string for 0xc002f313 failed with error 0xc02090f3. Make sure all message related files are registered.
Error at FTP Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)"

When I edit the FTP Task, the FtpConnection is set properly. What am I doing wrong? Any help would be greatly appreciated. Thank you.

Clay Benoit

Hi Clay
This is a validation error telling you to complete the properties in the FTP dialog, either select send or the receive option with complete valid paths
for example of FTP please try the http://www.sqlis.com/

Thanks|||I get the same error:

Error: 0xC002918A at FTP Copy blahblah, FTP Task: Unable to receive files using "FTP Connection Manager".

Error: 0x80004005 at FTP Copy blahblah, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

I have a FTP component with wildcards on the files
(RemotePath: "/somewhere/myfile*.*")
OverwriteFileatDest : False
Local path is "Existing Folder" with proper local folder unc.

The errors come after the list of files that could not be copied because they already exist on the local machine.
any ideas?
Horseshoe

|||There are two problems:
It appears that your setup is corrupt because it cannot find the error messages file.

You're attempting to download files to a location where identically named files are stored yet you have OverwriteFileAtDest set to false. Move the files or set that property to true and the task should succeed.|||

This looks like a bug to me

The first error is valid - the file does not exist

Error: 0xC0029183 at FTP Delete XST from Remote, FTP Task: File represented by "User::XSTFile" does not exist.

Error: 0x80004005 at FTP Delete XST from Remote, FTP Task: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

The second looks like someone at MS fast fingered 29183 as 20918, and that message is not in the message files.

So you get two errors for the price of one...

Is there a way to make an expected error not increment the error count? Or do you just have to set the MaxErrors high enough to not have it matter?

|||

Here is what worked for me...

It turns out of course that the ftp site was unix. I knew the variable as /subdir/file.txt.gz but I could not get that file to download, much less the others I wanted to itterate. I tested localy using FileZilla FTP Server on a local windows box and it worked perfectly. It was not until I decided to NOT use my generated file location variable and to use a design time fixed variable that it became clear that a Unix path was needed. What looked to me in IE as /subdir/file was was really /export/home/subdir/subdir/file. Only after I specificaly picked the file I wanted with the FTP Task did the real path show up.

Hope this helps.

Error using Execute Package Task

I'm trying to run a package from another package using Execute package task -

I'm getting the error:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

I have Delay validation on the child package. Any other suggestions?

Thanks

I am perhaps wrong about this but I don't think setting DelayValidation=TRUE on the package prevents tasks from getting validated. If you have tasks that you don't want to validate when the package starts then set DelayValidation=TRUE on the TaskHost.

-Jamie

|||

I have every task/step in the child package set to delay validation. I can run it stand-alone no problem - just can't get it to run from another package

|||

A shot in the dark but do you have DelayValidation=TRUE on your connection managers as well?

-Jamie

|||I set them as well. I also tried disabling every step in the control flow of the child package and I still got the error|||

Then I can only think that one of your tasks is failing validation when it comes to execute. If this is the case you should have more details in the package log if you have one. And if you don't, you probably should.

-Jamie

|||

Not sure this helps much - I get the OnPostValidate and then the OnError with message that I mentioned at the beginning?

OnPostValidate,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:55 AM,2/9/2006 8:56:55 AM,0,0x,(null)

OnError,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:57 AM,2/9/2006 8:56:57 AM,-1073602332,0x,Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

|||

I had an identical scenario. The package would run standalone; however, I could not run the package from another package. I had DelayValidation=TRUE at the package level and all tasks in the package were DelayValidation=FALSE.

My resolution was to set DelayValidation=FALSE at the package level and to set DelayValidation=TRUE for all the tasks.

|||Thanks GoZags! I had the same issue and I struggled for hours this morning before coming across this post - I just wish I had looked here sooner, it would have saved me a ton of frustration!|||

To All:

We were getting the same error message.

However, we discovered that it was the result of setting up our package to run on an environment variable, creating the environment variables on the target SQL Server machine, but failed to restart SQL Server Services so that it would be aware of the new environment variables.

DOH!

|||

GoZags, Jamie thanks for your imput, this bit of information has helped me as well, it solved the error above.

Error using Execute Package Task

I'm trying to run a package from another package using Execute package task -

I'm getting the error:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

I have Delay validation on the child package. Any other suggestions?

Thanks

I am perhaps wrong about this but I don't think setting DelayValidation=TRUE on the package prevents tasks from getting validated. If you have tasks that you don't want to validate when the package starts then set DelayValidation=TRUE on the TaskHost.

-Jamie

|||

I have every task/step in the child package set to delay validation. I can run it stand-alone no problem - just can't get it to run from another package

|||

A shot in the dark but do you have DelayValidation=TRUE on your connection managers as well?

-Jamie

|||I set them as well. I also tried disabling every step in the control flow of the child package and I still got the error|||

Then I can only think that one of your tasks is failing validation when it comes to execute. If this is the case you should have more details in the package log if you have one. And if you don't, you probably should.

-Jamie

|||

Not sure this helps much - I get the OnPostValidate and then the OnError with message that I mentioned at the beginning?

OnPostValidate,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:55 AM,2/9/2006 8:56:55 AM,0,0x,(null)

OnError,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:57 AM,2/9/2006 8:56:57 AM,-1073602332,0x,Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

|||

I had an identical scenario. The package would run standalone; however, I could not run the package from another package. I had DelayValidation=TRUE at the package level and all tasks in the package were DelayValidation=FALSE.

My resolution was to set DelayValidation=FALSE at the package level and to set DelayValidation=TRUE for all the tasks.

|||Thanks GoZags! I had the same issue and I struggled for hours this morning before coming across this post - I just wish I had looked here sooner, it would have saved me a ton of frustration!|||

To All:

We were getting the same error message.

However, we discovered that it was the result of setting up our package to run on an environment variable, creating the environment variables on the target SQL Server machine, but failed to restart SQL Server Services so that it would be aware of the new environment variables.

DOH!

|||

GoZags, Jamie thanks for your imput, this bit of information has helped me as well, it solved the error above.

Error using Execute Package Task

I'm trying to run a package from another package using Execute package task -

I'm getting the error:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

I have Delay validation on the child package. Any other suggestions?

Thanks

I am perhaps wrong about this but I don't think setting DelayValidation=TRUE on the package prevents tasks from getting validated. If you have tasks that you don't want to validate when the package starts then set DelayValidation=TRUE on the TaskHost.

-Jamie

|||

I have every task/step in the child package set to delay validation. I can run it stand-alone no problem - just can't get it to run from another package

|||

A shot in the dark but do you have DelayValidation=TRUE on your connection managers as well?

-Jamie

|||I set them as well. I also tried disabling every step in the control flow of the child package and I still got the error|||

Then I can only think that one of your tasks is failing validation when it comes to execute. If this is the case you should have more details in the package log if you have one. And if you don't, you probably should.

-Jamie

|||

Not sure this helps much - I get the OnPostValidate and then the OnError with message that I mentioned at the beginning?

OnPostValidate,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:55 AM,2/9/2006 8:56:55 AM,0,0x,(null)

OnError,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:57 AM,2/9/2006 8:56:57 AM,-1073602332,0x,Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

|||

I had an identical scenario. The package would run standalone; however, I could not run the package from another package. I had DelayValidation=TRUE at the package level and all tasks in the package were DelayValidation=FALSE.

My resolution was to set DelayValidation=FALSE at the package level and to set DelayValidation=TRUE for all the tasks.

|||Thanks GoZags! I had the same issue and I struggled for hours this morning before coming across this post - I just wish I had looked here sooner, it would have saved me a ton of frustration!|||

To All:

We were getting the same error message.

However, we discovered that it was the result of setting up our package to run on an environment variable, creating the environment variables on the target SQL Server machine, but failed to restart SQL Server Services so that it would be aware of the new environment variables.

DOH!

|||

GoZags, Jamie thanks for your imput, this bit of information has helped me as well, it solved the error above.

Error using Execute Package Task

I'm trying to run a package from another package using Execute package task -

I'm getting the error:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

I have Delay validation on the child package. Any other suggestions?

Thanks

I am perhaps wrong about this but I don't think setting DelayValidation=TRUE on the package prevents tasks from getting validated. If you have tasks that you don't want to validate when the package starts then set DelayValidation=TRUE on the TaskHost.

-Jamie

|||

I have every task/step in the child package set to delay validation. I can run it stand-alone no problem - just can't get it to run from another package

|||

A shot in the dark but do you have DelayValidation=TRUE on your connection managers as well?

-Jamie

|||I set them as well. I also tried disabling every step in the control flow of the child package and I still got the error|||

Then I can only think that one of your tasks is failing validation when it comes to execute. If this is the case you should have more details in the package log if you have one. And if you don't, you probably should.

-Jamie

|||

Not sure this helps much - I get the OnPostValidate and then the OnError with message that I mentioned at the beginning?

OnPostValidate,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:55 AM,2/9/2006 8:56:55 AM,0,0x,(null)

OnError,SARASINXPLT,VISIBILITY\sarasin,Payroll Stage,{AA054E03-67C2-4511-898E-58706A82A16B},{5F75D1F4-52F4-47CF-85B0-9F5542364939},2/9/2006 8:56:57 AM,2/9/2006 8:56:57 AM,-1073602332,0x,Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

|||

I had an identical scenario. The package would run standalone; however, I could not run the package from another package. I had DelayValidation=TRUE at the package level and all tasks in the package were DelayValidation=FALSE.

My resolution was to set DelayValidation=FALSE at the package level and to set DelayValidation=TRUE for all the tasks.

|||Thanks GoZags! I had the same issue and I struggled for hours this morning before coming across this post - I just wish I had looked here sooner, it would have saved me a ton of frustration!|||

To All:

We were getting the same error message.

However, we discovered that it was the result of setting up our package to run on an environment variable, creating the environment variables on the target SQL Server machine, but failed to restart SQL Server Services so that it would be aware of the new environment variables.

DOH!

|||

GoZags, Jamie thanks for your imput, this bit of information has helped me as well, it solved the error above.

|||Hey thank you it solved my problem. But, it is Weird that we need to leave DelayValidation=FALSE at the package level but set DelayValidation=TRUE for all the tasks..? anyway it works thanks for clarifying this.

ERROR USING BULK INSERT

Hi
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:

> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.

> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:

>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>

Error Starting Script Editor in SSIS

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

How odd! I havent' idea.. Maybe this is a stuff related with the own assembly?

Look at c:\windows\assembly and make sure that exists Microsoft.Vsa.Hosting.Design Time assembly.

I'm sorry

|||

reedcbr wrote:

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

does other control flow objects and data flow transformation experience the same

|||Those are the only two that I have noticed it in. Are there other objects that have Design Script buttons. If so, I will test them and reply with an answer.|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||I am wondering if anyone is monitoring this forum, I posted answers to the questions that I was asked and haven't received any more responses.|||Well, perhaps a reinstall is in order... Nevermind... Just read the whole thread.|||I was just wondering if this could be happening because this desktop is running Windows XP Media Edition?|||

Do you have Visual Studio installed?

If yes, can you try and and run : Tools-> Macros-> Macros IDE... and see if that works?

Thanks,

Ovidiu

|||

hi,

Weird thing happen to SSIS when

the integration service does not have sufficient right

in the Box.

check to see if the integrarion service has sufficient rights

click start>run>type "service.msc"

look for the integration service and supply it with a powerful account in your box

perhaps an administrator account.

regards,

joey

|||

I'm having the same problem, except when I click the Design Script button nothing happens, I don't even get an error message. Has anyone been able to find the solution for this?

Tom

|||

(I think the underlying cause is something to do with .Net 3.0 install, Maybe the installation of SQL2005 SP2, or maybe the order of install between VS2005 and SSIS, but whatever the reason, there is a registry issue.

First, do make sure your SSIS service has appropriate login rights.

Also, navigate to the following folders:

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa

In each of those folders run this command to re-register everything in there.:

for %i in (*.dll) do RegSvr32 -s %i

You will be very pleased with the results I think. May be a little blunt, but it worked for me.

I had determined this was the issue from the detailed error message provided in the dialog, where it indicated the problem was the program Microsoft.VisualBasic.Vsa.Dt.get_extensibility.... blah blah, and another message saying "interface not registered". Putting 2 and 2 together, I did a search for that dll, and registered everything in that folder and the other one.

There are a few people on forums starting to post this issue, and there don't seem to be any responses, so tell everyone you know, because I'm sure it will be a common problem among SSIS developers who use scripting.

|||

Your idea is on the right path. My initial error pointed me to the DTS folder under C:\Program Files\Microsoft SQL Server\90\DTS\Binn. I registered what you did above, but it did not work. I decided, hey lets try the DTS\Binn folder. What do you know, I no longer get the error and can see all my work flow in SSIS. Thanks for the tip (it appears security does not have to be set to Local User, but rather a solid Admin account).

Thanks,

TR

Error Starting Script Editor in SSIS

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

How odd! I havent' idea.. Maybe this is a stuff related with the own assembly?

Look at c:\windows\assembly and make sure that exists Microsoft.Vsa.Hosting.Design Time assembly.

I'm sorry

|||

reedcbr wrote:

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

does other control flow objects and data flow transformation experience the same

|||Those are the only two that I have noticed it in. Are there other objects that have Design Script buttons. If so, I will test them and reply with an answer.|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||I am wondering if anyone is monitoring this forum, I posted answers to the questions that I was asked and haven't received any more responses.|||Well, perhaps a reinstall is in order... Nevermind... Just read the whole thread.|||I was just wondering if this could be happening because this desktop is running Windows XP Media Edition?|||

Do you have Visual Studio installed?

If yes, can you try and and run : Tools-> Macros-> Macros IDE... and see if that works?

Thanks,

Ovidiu

|||

hi,

Weird thing happen to SSIS when

the integration service does not have sufficient right

in the Box.

check to see if the integrarion service has sufficient rights

click start>run>type "service.msc"

look for the integration service and supply it with a powerful account in your box

perhaps an administrator account.

regards,

joey

|||

I'm having the same problem, except when I click the Design Script button nothing happens, I don't even get an error message. Has anyone been able to find the solution for this?

Tom

|||

(I think the underlying cause is something to do with .Net 3.0 install, Maybe the installation of SQL2005 SP2, or maybe the order of install between VS2005 and SSIS, but whatever the reason, there is a registry issue.

First, do make sure your SSIS service has appropriate login rights.

Also, navigate to the following folders:

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa

In each of those folders run this command to re-register everything in there.:

for %i in (*.dll) do RegSvr32 -s %i

You will be very pleased with the results I think. May be a little blunt, but it worked for me.

I had determined this was the issue from the detailed error message provided in the dialog, where it indicated the problem was the program Microsoft.VisualBasic.Vsa.Dt.get_extensibility.... blah blah, and another message saying "interface not registered". Putting 2 and 2 together, I did a search for that dll, and registered everything in that folder and the other one.

There are a few people on forums starting to post this issue, and there don't seem to be any responses, so tell everyone you know, because I'm sure it will be a common problem among SSIS developers who use scripting.

|||

Your idea is on the right path. My initial error pointed me to the DTS folder under C:\Program Files\Microsoft SQL Server\90\DTS\Binn. I registered what you did above, but it did not work. I decided, hey lets try the DTS\Binn folder. What do you know, I no longer get the error and can see all my work flow in SSIS. Thanks for the tip (it appears security does not have to be set to Local User, but rather a solid Admin account).

Thanks,

TR

Error Starting Script Editor in SSIS

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

How odd! I havent' idea.. Maybe this is a stuff related with the own assembly?

Look at c:\windows\assembly and make sure that exists Microsoft.Vsa.Hosting.Design Time assembly.

I'm sorry

|||

reedcbr wrote:

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

does other control flow objects and data flow transformation experience the same

|||Those are the only two that I have noticed it in. Are there other objects that have Design Script buttons. If so, I will test them and reply with an answer.|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||I am wondering if anyone is monitoring this forum, I posted answers to the questions that I was asked and haven't received any more responses.|||Well, perhaps a reinstall is in order... Nevermind... Just read the whole thread.|||I was just wondering if this could be happening because this desktop is running Windows XP Media Edition?|||

Do you have Visual Studio installed?

If yes, can you try and and run : Tools-> Macros-> Macros IDE... and see if that works?

Thanks,

Ovidiu

|||

hi,

Weird thing happen to SSIS when

the integration service does not have sufficient right

in the Box.

check to see if the integrarion service has sufficient rights

click start>run>type "service.msc"

look for the integration service and supply it with a powerful account in your box

perhaps an administrator account.

regards,

joey

|||

I'm having the same problem, except when I click the Design Script button nothing happens, I don't even get an error message. Has anyone been able to find the solution for this?

Tom

|||

(I think the underlying cause is something to do with .Net 3.0 install, Maybe the installation of SQL2005 SP2, or maybe the order of install between VS2005 and SSIS, but whatever the reason, there is a registry issue.

First, do make sure your SSIS service has appropriate login rights.

Also, navigate to the following folders:

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa

In each of those folders run this command to re-register everything in there.:

for %i in (*.dll) do RegSvr32 -s %i

You will be very pleased with the results I think. May be a little blunt, but it worked for me.

I had determined this was the issue from the detailed error message provided in the dialog, where it indicated the problem was the program Microsoft.VisualBasic.Vsa.Dt.get_extensibility.... blah blah, and another message saying "interface not registered". Putting 2 and 2 together, I did a search for that dll, and registered everything in that folder and the other one.

There are a few people on forums starting to post this issue, and there don't seem to be any responses, so tell everyone you know, because I'm sure it will be a common problem among SSIS developers who use scripting.

|||

Your idea is on the right path. My initial error pointed me to the DTS folder under C:\Program Files\Microsoft SQL Server\90\DTS\Binn. I registered what you did above, but it did not work. I decided, hey lets try the DTS\Binn folder. What do you know, I no longer get the error and can see all my work flow in SSIS. Thanks for the tip (it appears security does not have to be set to Local User, but rather a solid Admin account).

Thanks,

TR

Error Starting Script Editor in SSIS

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

How odd! I havent' idea.. Maybe this is a stuff related with the own assembly?

Look at c:\windows\assembly and make sure that exists Microsoft.Vsa.Hosting.Design Time assembly.

I'm sorry

|||

reedcbr wrote:

I am having a problem in the Business Intelligence Development Studio. When I add and try to edit a Script Task or a Script Component, I click on the Design Script button and get one of two errors. For the Script task I get an error that states:

Cannot show the editor for this task. (Microsoft Visual Studio)

For the Script component I get:

Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)

For some reason or another it just will not start Visual Studio for Applications. I have uninstalled an re-installed several times. I have also researched and discovered that there is a bug when C# tasks are include in the project. However, this is not the case. Please help!

does other control flow objects and data flow transformation experience the same

|||Those are the only two that I have noticed it in. Are there other objects that have Design Script buttons. If so, I will test them and reply with an answer.|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||Yes, the assembly is there. Its' version is 8.0.0.0 and its' public key token is b03f5f7f11d50a3a|||I am wondering if anyone is monitoring this forum, I posted answers to the questions that I was asked and haven't received any more responses.|||Well, perhaps a reinstall is in order... Nevermind... Just read the whole thread.|||I was just wondering if this could be happening because this desktop is running Windows XP Media Edition?|||

Do you have Visual Studio installed?

If yes, can you try and and run : Tools-> Macros-> Macros IDE... and see if that works?

Thanks,

Ovidiu

|||

hi,

Weird thing happen to SSIS when

the integration service does not have sufficient right

in the Box.

check to see if the integrarion service has sufficient rights

click start>run>type "service.msc"

look for the integration service and supply it with a powerful account in your box

perhaps an administrator account.

regards,

joey

|||

I'm having the same problem, except when I click the Design Script button nothing happens, I don't even get an error message. Has anyone been able to find the solution for this?

Tom

|||

(I think the underlying cause is something to do with .Net 3.0 install, Maybe the installation of SQL2005 SP2, or maybe the order of install between VS2005 and SSIS, but whatever the reason, there is a registry issue.

First, do make sure your SSIS service has appropriate login rights.

Also, navigate to the following folders:

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\common\

C:\Program Files\Common Files\Microsoft Shared\VSA\8.0\vsa

In each of those folders run this command to re-register everything in there.:

for %i in (*.dll) do RegSvr32 -s %i

You will be very pleased with the results I think. May be a little blunt, but it worked for me.

I had determined this was the issue from the detailed error message provided in the dialog, where it indicated the problem was the program Microsoft.VisualBasic.Vsa.Dt.get_extensibility.... blah blah, and another message saying "interface not registered". Putting 2 and 2 together, I did a search for that dll, and registered everything in that folder and the other one.

There are a few people on forums starting to post this issue, and there don't seem to be any responses, so tell everyone you know, because I'm sure it will be a common problem among SSIS developers who use scripting.

|||

Your idea is on the right path. My initial error pointed me to the DTS folder under C:\Program Files\Microsoft SQL Server\90\DTS\Binn. I registered what you did above, but it did not work. I decided, hey lets try the DTS\Binn folder. What do you know, I no longer get the error and can see all my work flow in SSIS. Thanks for the tip (it appears security does not have to be set to Local User, but rather a solid Admin account).

Thanks,

TR

error running ssis package

I run an ssis task with right click in the BIDS.

the task transform data of the tables between sql2005 DB to another sql2005 DB.

this is the error I get:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"TCP Provider: The semaphore timeout period has expired.

".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Task failed: CopyTables2

i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.

for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.

Anyone knows the reason to the error.

Thanks.

It sounds like a timeout issue. You say that the task which is failing is moving a large amount of data, and taking more than 30 minutes? Is it always failing after the same amount of time? If so, you might check the timeout on the connections.

|||

it looks like a timeout issue also to me. when i remove a very big table from this package (a 40 milion records table) the package run faster and finish successfully. how can i set the timeout of the connection in the BIDS. i didn't see timeout property in the connections manager.

anyway, i dont understand this SSIS. in sql2000 i transferred very large tables with no problem using the dts. since i upgraded to sql2005 i encounter lots of bugs in the SSIS.

Thanks.

|||

Open up your connection manager, then click on the 'All' page, under the 'Initialization' category there is a 'connect timeout' and 'general timeout' properties. It seems like by default these are both set to 0, which I would interpret as unlimited though.

Could you give a more detailed explanation of your package. Are you transferring the data through a stored procedure in an execute sql task or a data flow task?

|||You might also check the SQL Server to make sure the query governor is not enabled. The setting is located in the Server Properties, under the Connections page.|||

Thanks for your answer.

I'm sorry, i can't find the 'all' page and i can't find the 'initialization' catrgory in the connections manager.

I created a task in the control flow in the BIDS. i dragged from the toolbox to the control flow, transfer sql server object. i configured the source DB and destination DB and the tables I want to transfer. I only transder the data in the tables.

appreciate your help.

|||The query governor is indeed not enabled. thx.|||

When you open the connection manager, you should see Connection and All listed on the left hand side of the dialog box. Click All to see all the information realted to the connection.

|||

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

|||

kubyustus wrote:

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

Hi,

Please double-click the icon of the "Connection" in the "Connection Managers" tab.

You get a window called "Connection Manager".

Here you must have a left pane where you see "Connection" which should be highlighted.

In the main portion of the window you see the "Server Name", "Log on to the server" (for Authentication) and "Connect to a database".

On the left pane, just below "Connection" you should be able to find a tab called "All". Click on this tab and the main portion of the window should now show you properties like "Advanced", "Initialization", "Named ConnectionString", "Pooling", "Security", etc.

Please check the value against "Initialization" > "Connect Timeout".

Thanks and Regards,

B@.ns

|||

in the BIDS:

i double click the icon of the "Connection" and got the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. i didn't find the window called "Connection Manager".

where is it ?

|||OK, it finally clicked. You are using the Transfer SQL Server Objects task. Try using a data flow with an OLEDB Source and OLEDB Destination instead.|||

yes, I'm using the sql server object task.

I try now the data flow oleDB source and destination.

now when I double click the connection in the connection manager i indeed see the 'ALL' in the left side.

the value 0 is what I need for the connection and general timeout property, right?

However, I'm not so familiar with this way of transfering data. i dragged the oleDB source and oleDB destination from the toolbox. i configured the connections but i see there an option to select only one table to transfer. i need to transfer data of many tables.

in the oleDB source editor dialog window i have: oleDB connection manager, then data access mode where i can choose table of view, sql command or sql command from variable and then i can choose table or view, but only one.

How can I transfer many tables?

Thanks alot for your answers.

|||You'd have to create more data flows with their own source and destination. I'd recommend that you use the SQL Server Transfer Objects task to move your smaller tables, and use the data flow tasks to move only the large tables.|||Thanks, I will try it and let u know.

error running ssis package

I run an ssis task with right click in the BIDS.

the task transform data of the tables between sql2005 DB to another sql2005 DB.

this is the error I get:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"TCP Provider: The semaphore timeout period has expired.

".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Task failed: CopyTables2

i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.

for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.

Anyone knows the reason to the error.

Thanks.

It sounds like a timeout issue. You say that the task which is failing is moving a large amount of data, and taking more than 30 minutes? Is it always failing after the same amount of time? If so, you might check the timeout on the connections.

|||

it looks like a timeout issue also to me. when i remove a very big table from this package (a 40 milion records table) the package run faster and finish successfully. how can i set the timeout of the connection in the BIDS. i didn't see timeout property in the connections manager.

anyway, i dont understand this SSIS. in sql2000 i transferred very large tables with no problem using the dts. since i upgraded to sql2005 i encounter lots of bugs in the SSIS.

Thanks.

|||

Open up your connection manager, then click on the 'All' page, under the 'Initialization' category there is a 'connect timeout' and 'general timeout' properties. It seems like by default these are both set to 0, which I would interpret as unlimited though.

Could you give a more detailed explanation of your package. Are you transferring the data through a stored procedure in an execute sql task or a data flow task?

|||You might also check the SQL Server to make sure the query governor is not enabled. The setting is located in the Server Properties, under the Connections page.|||

Thanks for your answer.

I'm sorry, i can't find the 'all' page and i can't find the 'initialization' catrgory in the connections manager.

I created a task in the control flow in the BIDS. i dragged from the toolbox to the control flow, transfer sql server object. i configured the source DB and destination DB and the tables I want to transfer. I only transder the data in the tables.

appreciate your help.

|||The query governor is indeed not enabled. thx.|||

When you open the connection manager, you should see Connection and All listed on the left hand side of the dialog box. Click All to see all the information realted to the connection.

|||

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

|||

kubyustus wrote:

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

Hi,

Please double-click the icon of the "Connection" in the "Connection Managers" tab.

You get a window called "Connection Manager".

Here you must have a left pane where you see "Connection" which should be highlighted.

In the main portion of the window you see the "Server Name", "Log on to the server" (for Authentication) and "Connect to a database".

On the left pane, just below "Connection" you should be able to find a tab called "All". Click on this tab and the main portion of the window should now show you properties like "Advanced", "Initialization", "Named ConnectionString", "Pooling", "Security", etc.

Please check the value against "Initialization" > "Connect Timeout".

Thanks and Regards,

B@.ns

|||

in the BIDS:

i double click the icon of the "Connection" and got the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. i didn't find the window called "Connection Manager".

where is it ?

|||OK, it finally clicked. You are using the Transfer SQL Server Objects task. Try using a data flow with an OLEDB Source and OLEDB Destination instead.|||

yes, I'm using the sql server object task.

I try now the data flow oleDB source and destination.

now when I double click the connection in the connection manager i indeed see the 'ALL' in the left side.

the value 0 is what I need for the connection and general timeout property, right?

However, I'm not so familiar with this way of transfering data. i dragged the oleDB source and oleDB destination from the toolbox. i configured the connections but i see there an option to select only one table to transfer. i need to transfer data of many tables.

in the oleDB source editor dialog window i have: oleDB connection manager, then data access mode where i can choose table of view, sql command or sql command from variable and then i can choose table or view, but only one.

How can I transfer many tables?

Thanks alot for your answers.

|||You'd have to create more data flows with their own source and destination. I'd recommend that you use the SQL Server Transfer Objects task to move your smaller tables, and use the data flow tasks to move only the large tables.|||Thanks, I will try it and let u know.

error running ssis package

I run an ssis task with right click in the BIDS.

the task transform data of the tables between sql2005 DB to another sql2005 DB.

this is the error I get:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"TCP Provider: The semaphore timeout period has expired.

".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Task failed: CopyTables2

i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.

for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.

Anyone knows the reason to the error.

Thanks.

It sounds like a timeout issue. You say that the task which is failing is moving a large amount of data, and taking more than 30 minutes? Is it always failing after the same amount of time? If so, you might check the timeout on the connections.

|||

it looks like a timeout issue also to me. when i remove a very big table from this package (a 40 milion records table) the package run faster and finish successfully. how can i set the timeout of the connection in the BIDS. i didn't see timeout property in the connections manager.

anyway, i dont understand this SSIS. in sql2000 i transferred very large tables with no problem using the dts. since i upgraded to sql2005 i encounter lots of bugs in the SSIS.

Thanks.

|||

Open up your connection manager, then click on the 'All' page, under the 'Initialization' category there is a 'connect timeout' and 'general timeout' properties. It seems like by default these are both set to 0, which I would interpret as unlimited though.

Could you give a more detailed explanation of your package. Are you transferring the data through a stored procedure in an execute sql task or a data flow task?

|||You might also check the SQL Server to make sure the query governor is not enabled. The setting is located in the Server Properties, under the Connections page.|||

Thanks for your answer.

I'm sorry, i can't find the 'all' page and i can't find the 'initialization' catrgory in the connections manager.

I created a task in the control flow in the BIDS. i dragged from the toolbox to the control flow, transfer sql server object. i configured the source DB and destination DB and the tables I want to transfer. I only transder the data in the tables.

appreciate your help.

|||The query governor is indeed not enabled. thx.|||

When you open the connection manager, you should see Connection and All listed on the left hand side of the dialog box. Click All to see all the information realted to the connection.

|||

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

|||

kubyustus wrote:

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

Hi,

Please double-click the icon of the "Connection" in the "Connection Managers" tab.

You get a window called "Connection Manager".

Here you must have a left pane where you see "Connection" which should be highlighted.

In the main portion of the window you see the "Server Name", "Log on to the server" (for Authentication) and "Connect to a database".

On the left pane, just below "Connection" you should be able to find a tab called "All". Click on this tab and the main portion of the window should now show you properties like "Advanced", "Initialization", "Named ConnectionString", "Pooling", "Security", etc.

Please check the value against "Initialization" > "Connect Timeout".

Thanks and Regards,

B@.ns

|||

in the BIDS:

i double click the icon of the "Connection" and got the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. i didn't find the window called "Connection Manager".

where is it ?

|||OK, it finally clicked. You are using the Transfer SQL Server Objects task. Try using a data flow with an OLEDB Source and OLEDB Destination instead.|||

yes, I'm using the sql server object task.

I try now the data flow oleDB source and destination.

now when I double click the connection in the connection manager i indeed see the 'ALL' in the left side.

the value 0 is what I need for the connection and general timeout property, right?

However, I'm not so familiar with this way of transfering data. i dragged the oleDB source and oleDB destination from the toolbox. i configured the connections but i see there an option to select only one table to transfer. i need to transfer data of many tables.

in the oleDB source editor dialog window i have: oleDB connection manager, then data access mode where i can choose table of view, sql command or sql command from variable and then i can choose table or view, but only one.

How can I transfer many tables?

Thanks alot for your answers.

|||You'd have to create more data flows with their own source and destination. I'd recommend that you use the SQL Server Transfer Objects task to move your smaller tables, and use the data flow tasks to move only the large tables.|||Thanks, I will try it and let u know.

error running ssis package

I run an ssis task with right click in the BIDS.

the task transform data of the tables between sql2005 DB to another sql2005 DB.

this is the error I get:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"TCP Provider: The semaphore timeout period has expired.

".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Task failed: CopyTables2

i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.

for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.

Anyone knows the reason to the error.

Thanks.

It sounds like a timeout issue. You say that the task which is failing is moving a large amount of data, and taking more than 30 minutes? Is it always failing after the same amount of time? If so, you might check the timeout on the connections.

|||

it looks like a timeout issue also to me. when i remove a very big table from this package (a 40 milion records table) the package run faster and finish successfully. how can i set the timeout of the connection in the BIDS. i didn't see timeout property in the connections manager.

anyway, i dont understand this SSIS. in sql2000 i transferred very large tables with no problem using the dts. since i upgraded to sql2005 i encounter lots of bugs in the SSIS.

Thanks.

|||

Open up your connection manager, then click on the 'All' page, under the 'Initialization' category there is a 'connect timeout' and 'general timeout' properties. It seems like by default these are both set to 0, which I would interpret as unlimited though.

Could you give a more detailed explanation of your package. Are you transferring the data through a stored procedure in an execute sql task or a data flow task?

|||You might also check the SQL Server to make sure the query governor is not enabled. The setting is located in the Server Properties, under the Connections page.|||

Thanks for your answer.

I'm sorry, i can't find the 'all' page and i can't find the 'initialization' catrgory in the connections manager.

I created a task in the control flow in the BIDS. i dragged from the toolbox to the control flow, transfer sql server object. i configured the source DB and destination DB and the tables I want to transfer. I only transder the data in the tables.

appreciate your help.

|||The query governor is indeed not enabled. thx.|||

When you open the connection manager, you should see Connection and All listed on the left hand side of the dialog box. Click All to see all the information realted to the connection.

|||

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

|||

kubyustus wrote:

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it?

Sorry for all the questions...

Thanks.

Hi,

Please double-click the icon of the "Connection" in the "Connection Managers" tab.

You get a window called "Connection Manager".

Here you must have a left pane where you see "Connection" which should be highlighted.

In the main portion of the window you see the "Server Name", "Log on to the server" (for Authentication) and "Connect to a database".

On the left pane, just below "Connection" you should be able to find a tab called "All". Click on this tab and the main portion of the window should now show you properties like "Advanced", "Initialization", "Named ConnectionString", "Pooling", "Security", etc.

Please check the value against "Initialization" > "Connect Timeout".

Thanks and Regards,

B@.ns

|||

in the BIDS:

i double click the icon of the "Connection" and got the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. i didn't find the window called "Connection Manager".

where is it ?

|||OK, it finally clicked. You are using the Transfer SQL Server Objects task. Try using a data flow with an OLEDB Source and OLEDB Destination instead.|||

yes, I'm using the sql server object task.

I try now the data flow oleDB source and destination.

now when I double click the connection in the connection manager i indeed see the 'ALL' in the left side.

the value 0 is what I need for the connection and general timeout property, right?

However, I'm not so familiar with this way of transfering data. i dragged the oleDB source and oleDB destination from the toolbox. i configured the connections but i see there an option to select only one table to transfer. i need to transfer data of many tables.

in the oleDB source editor dialog window i have: oleDB connection manager, then data access mode where i can choose table of view, sql command or sql command from variable and then i can choose table or view, but only one.

How can I transfer many tables?

Thanks alot for your answers.

|||You'd have to create more data flows with their own source and destination. I'd recommend that you use the SQL Server Transfer Objects task to move your smaller tables, and use the data flow tasks to move only the large tables.|||Thanks, I will try it and let u know.