Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

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

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc

Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

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

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc

Error: unable to retrieve column information from the data source

Hi,

I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.

In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.

Thanks in advance.
Asim.

Anyone?

|||I am having the same problem. I assume it is because the stored procedure I am using is long running (1min 20s) The designer looks like it starts to run the sproc to get the metadata, but then gives up after 15 or 30 seconds. Any solutions?|||

Hi,

If your final select statement is for a temporary table, u will get this problem. Use table variable or actual table instead of temp table.

|||

Yes, I figured out a solution. Basically create a hard coded resultset (select stmt) at the top of the procedure with the same columns as the actual resultset.

That did it for me.

Asim.

|||

Ok, that solution does not work. By adding the header, the package compiles and runs, but the row from the header is inserted in the destination, not from the (actual) second resultset. So I am back to square one.

Can anyone please help.

Asim.

|||This solved my problem. However, it was not enough to change the last select from a temp table to table variable. I had to change all occurences of temp tables to table variables. I also found that it sped up my query by a factor of 16x from 1min 20s to 5s.|||My workaround is to create output columns that are being returned by the query manually.

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipelin

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"

-Jamie

|||

In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.

|||

And the registry...?

-Jamie

|||

I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:

\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read

|||

run regedit

press CTRL-F

search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID

-Jamie

|||

Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.

Please get back to me a.s.a.p.

David

|||Hi all, I have the same problem and no sulution for it......hmmm|||

I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.

|||

Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.

|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipelin

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"

-Jamie

|||

In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.

|||

And the registry...?

-Jamie

|||

I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:

\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read

|||

run regedit

press CTRL-F

search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID

-Jamie

|||

Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.

Please get back to me a.s.a.p.

David

|||Hi all, I have the same problem and no sulution for it......hmmm|||

I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.

|||

Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.

|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipelin

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"

-Jamie

|||

In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.

|||

And the registry...?

-Jamie

|||

I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:

\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read

|||

run regedit

press CTRL-F

search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID

-Jamie

|||

Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.

Please get back to me a.s.a.p.

David

|||Hi all, I have the same problem and no sulution for it......hmmm|||

I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.

|||

Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.

|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pip

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"

-Jamie

|||

In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.

|||

And the registry...?

-Jamie

|||

I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:

\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read

|||

run regedit

press CTRL-F

search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID

-Jamie

|||

Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.

Please get back to me a.s.a.p.

David

|||Hi all, I have the same problem and no sulution for it......hmmm|||

I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.

|||

Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.

|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.

Error: The task with the name "Data Flow Task" and the creation name "DTS.Pip

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"

-Jamie

|||

In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.

|||

And the registry...?

-Jamie

|||

I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:

\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read

|||

run regedit

press CTRL-F

search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID

-Jamie

|||

Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.

Please get back to me a.s.a.p.

David

|||Hi all, I have the same problem and no sulution for it......hmmm|||

I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.

|||

Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.

|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.

Error: The external metadata column collection is out of synchronization with the data source co

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.

I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.

On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....

What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.

Can anyone help me to resolve this issue.

Thanks.

JayaC wrote:

Hello,

I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.

I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.

On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:

Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....

What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.

Can anyone help me to resolve this issue.

Thanks.

I can only guess that it is looking at the wrong data source or something. This is most likely symptomatic of something else. Check that the account you are running SQL Agent as has got all the relevant permissions.

-Jamie

|||

Thanks Jamie,

Account under which the SQL Server Agent was running was causing the problem- Permissions Issue.

Error: The connection manager failed to defect from the transaction.

Does anybody know what it means?

The context is: a package with a single Data Flow finishes with this error: [Connection manager "xxx"] Error: The connection manager failed to defect from the transaction.

All components inside the data flow reports a successfull green status, however the DataFlow Task remains yellow for a long time, when suddenly the package stops with the above message. Another information is that the table remains locked during package execution (a select on that table does not complete...). This is a serious issue, and I couldnt find any relevant information about that. Thanks in advance.

Hi Half Abude,

Can you tell us more about the package? What kind of SQL is being executed? Also I'm just guessing you're using transactions in the package, can you tell us how you've set the transaction boundaries in the package? (i.e. on what object(s) have you set TransactionOption to Required?)

Thanks, -David