SQL Server Management Studio: hii

Showing posts with label hii. Show all posts

Friday, March 23, 2012

Migrate stand alone sql server to a cluster without downtime

Hi
I have read most the articles and posts about move sql server.
Here is my situation:
I have stand alone standard sql server 2000 and with about 40 databases. And
I am moving the server to a SQL cluster(Active/passive). I have to use the
old name, IP on the new server and with minimum down time. Anybody has done
this? What is the precedure? Will Virutas BackupExe backup/restore work? I
know Deattach/Reattach works but I have to do it one DB at a time, right?
That will take a long time. Will permission be carried over using these
mothed?
Any information will be greatly appreciated.
Thanks,
Yuhong
First, you cannot use the same name and IP address. The cluster
installation and any data transfer will fail.
Here is how you do this.
First, make sure all your SQL connections to the old box use a DNS entry to
resolve their names. Eliminate any hard-coded IP addresses by using client
aliases if necessary. You will use an SVC record in the DNS system to
repoint the old connections to the new server when you do the changeover.
Test this using a bogus alias before going live.
Set all your production databases to full recovery and implement a log
backup plan.
Use backup and restore to create the databases on the new system. Use the
'NORECOVERY' or 'STANDBY' option so you can apply later transaction logs.
Manually copy and apply the transaction log backups from the old system to
the new cluster.
I suggest scripting the following tasks well ahead of time.
Run a log backup WITH STANDBY on each database on the old server. Copy the
files (or restore from a common network share) and restore the final log for
each database using the WITH RECOVERY option.
When you have transferred the final database log, use sp_change_users_login
to remap your pre-generated logins to the users in the restored databases.
I typically use a bogus password for SQL logins until this step so that
users cannot accidentally connect to the new server.
Stop the old server. Add the SVC record to DNS to alias the old servername
to the new service. Remember to include the port number if you are using a
named instance or a non-standard port.
Get ready to deal with the handful of systems you missed that are still
connecting via IP.
Remember to test everything before going live. If you mess up, you can
always bring the old server back up and take the databases out of standby to
get back to where you started. The actual changeover should take less than
one hour IF you plan, test, and practice.
Good luck.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:59B178D2-D730-4142-BE76-E0A38FE53F69@.microsoft.com...
> Hi
> I have read most the articles and posts about move sql server.
> Here is my situation:
> I have stand alone standard sql server 2000 and with about 40 databases.
And
> I am moving the server to a SQL cluster(Active/passive). I have to use the
> old name, IP on the new server and with minimum down time. Anybody has
done
> this? What is the precedure? Will Virutas BackupExe backup/restore work? I
> know Deattach/Reattach works but I have to do it one DB at a time, right?
> That will take a long time. Will permission be carried over using these
> mothed?
> Any information will be greatly appreciated.
> Thanks,
> --
> Yuhong
|||Thanks, Geoff! This is very helpful. I have setup a test cluster and I will
have to do a few full test before the real migration.
Thanks again!
Yuhong
"Geoff N. Hiten" wrote:

> First, you cannot use the same name and IP address. The cluster
> installation and any data transfer will fail.
> Here is how you do this.
> First, make sure all your SQL connections to the old box use a DNS entry to
> resolve their names. Eliminate any hard-coded IP addresses by using client
> aliases if necessary. You will use an SVC record in the DNS system to
> repoint the old connections to the new server when you do the changeover.
> Test this using a bogus alias before going live.
> Set all your production databases to full recovery and implement a log
> backup plan.
> Use backup and restore to create the databases on the new system. Use the
> 'NORECOVERY' or 'STANDBY' option so you can apply later transaction logs.
> Manually copy and apply the transaction log backups from the old system to
> the new cluster.
> I suggest scripting the following tasks well ahead of time.
> Run a log backup WITH STANDBY on each database on the old server. Copy the
> files (or restore from a common network share) and restore the final log for
> each database using the WITH RECOVERY option.
> When you have transferred the final database log, use sp_change_users_login
> to remap your pre-generated logins to the users in the restored databases.
> I typically use a bogus password for SQL logins until this step so that
> users cannot accidentally connect to the new server.
> Stop the old server. Add the SVC record to DNS to alias the old servername
> to the new service. Remember to include the port number if you are using a
> named instance or a non-standard port.
> Get ready to deal with the handful of systems you missed that are still
> connecting via IP.
> Remember to test everything before going live. If you mess up, you can
> always bring the old server back up and take the databases out of standby to
> get back to where you started. The actual changeover should take less than
> one hour IF you plan, test, and practice.
> Good luck.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:59B178D2-D730-4142-BE76-E0A38FE53F69@.microsoft.com...
> And
> done
>
>
sql

Migrate SQL Server 2000 to SQL Server 2005: login

Hi!
I'm trying to migrate (side-by-side) databases from SQL Server 2000 to SQL
Server 2005 and have had some problems with the logins and users, so I
wonder which is the "correct" way to make it right =)Ooops, I hit send accidently and sent this message before I was done with
it. =)
Here's the continuation:
So far I have used sp_help_revlogin and after that sp_change_users_login,
but the password for a login was wrong after this. Is there something else I
should do?
Thanks
//Malin

> Hi!
> I'm trying to migrate (side-by-side) databases from SQL Server 2000 to SQL
> Server 2005 and have had some problems with the logins and users, so I
> wonder which is the "correct" way to make it right =)
>|||Check out:
http://support.microsoft.com/defaul...kb;en-us;246133
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:ejLryIm1GHA.4264@.TK2MSFTNGP05.phx.gbl...
Hi!
I'm trying to migrate (side-by-side) databases from SQL Server 2000 to SQL
Server 2005 and have had some problems with the logins and users, so I
wonder which is the "correct" way to make it right =)|||The best way to do this is to transfer the logins with sp_help_revlogin to a
clean server - i.e. one with no logins. Do this just before you want to cut
over or advise your users that their password won't be current on the new
box if they change their passwords on the old box after you've transferred
them. You won't need sp_change_users_login in that case.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23Xf$aNm1GHA.4908@.TK2MSFTNGP02.phx.gbl...
Ooops, I hit send accidently and sent this message before I was done with
it. =)
Here's the continuation:
So far I have used sp_help_revlogin and after that sp_change_users_login,
but the password for a login was wrong after this. Is there something else I
should do?
Thanks
//Malin

> Hi!
> I'm trying to migrate (side-by-side) databases from SQL Server 2000 to SQL
> Server 2005 and have had some problems with the logins and users, so I
> wonder which is the "correct" way to make it right =)
>

Wednesday, March 21, 2012

migrate Oracle to sqlserver

Hi

I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.

Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5

How good is the migration assistant from m$$ for
migrating from oracle to sql2k5

Does it migrate data as well as procedural code ?

Do you anything for me to watch out for best parctices
migration guides

You opinion would be higly appreciated

regards
db2hrishyThere are a number of things to be aware of, the transition can be challenging, and once you've done one migration it will forever change how you view both Oracle and MS-SQL.

First and foremost, Oracle requires a row-oriented view of data in order to acheive good performance, and often to do anything at all. MS-SQL is exactly the opposite in that you need to abandon the row-oriented view of data (especially using cursors) in order to achieve good performance. This is a huge mind shift, and it is usually the hardest thing for a long time Oracle DBA to master.

Oracle provides PL/SQL, which spans several layers of the OSI model. MS-SQL provides many tools that together offer more functinality than PL/SQL, but they are many tools instead of just one tool. This can be a blessing or a curse depending on how you look at things.

The migration assistant provided by Microsoft is good, but like any generic migration tool it has both costs and benefits. Since I'm familiar with both Oracle and MS-SQL and have only had to migrate from Oracle to MS-SQL, I generally don't bother using any software assistant at all. As the auto makers are fond of saying: your milage may vary!

SQL-2005 provides two tools that make the migration of the data almost trivial. One tool that I couldn't live without for coversions is a Linked Server (http://support.microsoft.com/kb/280106). Another tool that is very powerful and you need to at least understand before you start is SSIS (http://msdn2.microsoft.com/en-us/library/ms141026.aspx) because it can offer simple solutions to otherwise noxious problems. There are more soltuions than just these two, but these are more than enough for 99.95% of the migrations that I've ever seen.

Schema migration can be a challenge, depending on how you/your business handles schema objects. If you have no existing migration controls (no SCCS, just brew the schema directly in production), then migration is relatively easy. If you have strict migration controls, then you need to determine the political issues, but technically it isn't going to be a big challenge. Unfortunately, most folks sit between the two extremes so the conversion can be charitably described as challenging. We might need to start a whole new thread for that discussion!

I don't know of anything that will migrate procedural code in a way that will suit me. I've seen many code migrators, a few of them produce working solutions, but I can't recommend any of them. I think that code needs to be manually migrated.

Anywho, I'll let you chew on this diatribe for the moment, but I've got lots more opinions if you are interested in them... Keep in mind that an opinion and a buck will get you a cup of coffee though (in other words, everybody has an opinion, but you can't trade an opinion for much of anything)!

-PatP|||Hi Phat

Thank you very much for taking time out and responding :-)

I have migrated (attempted) or tried migrating Db2 from mainframes to oracle and failed miserably :-) as i grossly underestimated the effort and here i am again trying to be brave :-)

The insights and experience some of you might provide is much valued by me.

I had a look SSMA(sqlserver migration assistant) and it claims to migrate Data as well as code anybody has working experience with this ?

For code migration i.e pl/sql
http://www.swisssql.com/
but it all depends on what the management thinks and if i can convince them for the $$ etc

For data migration i am thinking of SSIS or some open source ETL tool.

I have few questions though

1)Does anybody have a Migration template ?approach
for doing this sort of a activity which can be shared?
2)How good is SSMA for migrating data and PL/SQL to T-SQL
3)is SSIS free with SQL2k5 ?
4) When i migrate say a million row table from oracle to sql2k5 how do i ensure that all rows have been migrated and all the migrated rows are correct?

regards
Hrishy

Monday, March 12, 2012

Migrate Data Bases Different Server

Hi!!!
I have 2 servers running sql server enterprise 2000, however I will
shutdown one of them and will use only the newest.
I have to migrate 2 databases.. I was wondering which is the best way to do
so..
I thought that I can make a full backup and then restore the backup on the
new server but I dont know if this is possible...
or is there another way to do so? is it dangerous? easy?
Thanxs and
Greetings =)
http://support.microsoft.com/?kbid=224071
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
do
> so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>
|||Backup & Restore or attach & detach are usually the easiest but you might
want to have a look at these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
> do so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>
|||HI!!!
Thanxs a lot that really really helped =)
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
> do so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>
|||Or use DTS
Or use DataStudio from our company, check our website right here.
http://www.agileinfollc.com
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:OO8Rj1zoFHA.576@.TK2MSFTNGP15.phx.gbl...
> HI!!!
> Thanxs a lot that really really helped =)
>
> "Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
> news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
>

Migrate Data Bases Different Server

Hi!!!
I have 2 servers running sql server enterprise 2000, however I will
shutdown one of them and will use only the newest.
I have to migrate 2 databases.. I was wondering which is the best way to do
so..
I thought that I can make a full backup and then restore the backup on the
new server but I dont know if this is possible...
or is there another way to do so? is it dangerous? easy?
Thanxs and
Greetings =)http://support.microsoft.com/?kbid=224071
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
do
> so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>|||Backup & Restore or attach & detach are usually the easiest but you might
want to have a look at these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
> do so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>|||HI!!!
Thanxs a lot that really really helped =)
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> I have 2 servers running sql server enterprise 2000, however I will
> shutdown one of them and will use only the newest.
> I have to migrate 2 databases.. I was wondering which is the best way to
> do so..
> I thought that I can make a full backup and then restore the backup on the
> new server but I dont know if this is possible...
> or is there another way to do so? is it dangerous? easy?
> Thanxs and
> Greetings =)
>
>|||Or use DTS
Or use DataStudio from our company, check our website right here.
http://www.agileinfollc.com
"Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
news:OO8Rj1zoFHA.576@.TK2MSFTNGP15.phx.gbl...
> HI!!!
> Thanxs a lot that really really helped =)
>
> "Jess Gutirrez" <ardillapaw@.msn.com> wrote in message
> news:%23$3Ar7coFHA.3936@.TK2MSFTNGP10.phx.gbl...
>

Friday, March 9, 2012

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException

I have a SSIS project that has one parent package and three child packages. When I run the project on my development machine in debug mode it works fine. Also if i run the packages using dtexec on my development machine it still works fine. However the problem comes in when I try and run the project using dtexec on the staging server i get the following error:

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

does anyone have any idea how to fix this please?

thanks

Probably the data on staging server is different from the data on development machine?

You are trying to put data into buffer that is bigger than the buffer column size allocated for this data. E.g. script component builds some string, gets a string of length 60, and then tries to put it into STR column of length 50 - you'll get this exception as a result. You need to adjust the column size to match expected data, or cleanup the input data to avoid the overflow.

You probably don't see it on development machine, because your test data is different and this condition never occurs.

http://msdn2.microsoft.com/en-us/microsoft.sqlserver.dts.pipeline.doesnotfitbufferexception.aspx

|||thanks for the response but I am not sure that that is the problem since I am using the exact same data and exact same packages on both machines?|||

After some testing I have found that when I run the child packages in series as apposed to in parallel it all works fine. This should do for the mean time but if you have any idea why this is could u please let me know.

Thanks for the help.

Gary

|||

Hi again

I have a second package that has the same setup but manipulates a different data set and I am getting this same error but this time I can't find a way around it.

The data and the packages are exactly the same on both servers.On the development server the packages run with no problem but on the staging server I get buffer memory issues.

Anyone know why this is maybe?

It seems that the column width is too wide for the buffer so the buffer is overflowing into a second buffer which is being used by the other child package.

Any ideas?

thanks

|||

Pipeline Buffers are not shared between child and parent packages.

Could you describe what the packages are doing? What component reports this error? Do both dev and staging machine have the same architecture (32-bit, or 64-bit)?

|||

In the parent package data is extracted from multiple flat files and bulk inserted into multiple database table. Following this 4 child packages are initiated using the execute package task.

within each child package a data flow is started. In the data flow information is extracted from a table (populated in the parent package). The data is transformed using a script component. A row count is taken and then the data is inserted into a second database table.

After some investigation. I have discovered:

-on the development server the entire package works perfectly with no errors

-on the staging server each child package works when it is the only one executed from the parent package.

-on the staging server when I run two child packages it seems the first child package works fine but in the second package to be executed the script component does not get executed (I inserted a MessageBox and ComponentMetaData.FireInformation and there was no output).

This is the error message in the log when running all 4 child packages together:

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:12:57 PM,2006/06/29 02:12:57 PM,0,0x,The value is too large to fit in the column data area of the buffer. Record ID - Merchant Number (ODS: QMAKBACCTNBR) : 00000000000000000002063 MerchantNumber (ODS: QMAKBACCTNBR)OnInformation,,,Transform Data Into MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning.

OnInformation,,,ODS To DWH MerchantStatementsMonthly,,,2006/06/29 02:15:07 PM,2006/06/29 02:15:07 PM,1074016264,0x,Post Execute phase is beginning.

OnInformation,,,TrOnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:03 PM,2006/06/29 02:23:03 PM,-1073450910,0x,Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:06 PM,2006/06/29 02:23:06 PM,-1073450974,0x,The ProcessInput method on component "Script Component" (10641) failed with error code 0x80131600. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1071636284,0x,The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:07 PM,2006/06/29 02:23:07 PM,-1073450975,0x,Thread "WorkThread0" has exited with error code 0x80131600.

OnError,,,Transform Data Into MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

OnError,,,ODS To DWH MerchantMasterMonthly,,,2006/06/29 02:23:09 PM,2006/06/29 02:23:09 PM,-1073450952,0x,The PrimeOutput method on component "Q19400D" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

The development server is 32 bit and the staging server is 64bit but they are both runnning 32 bit operating systems.

Any help would be appreciated.

thanks

|||

We had somewhat the some problems as described above. We are implementing a datawarehouse-solution using sql2005 and an 64bit windows 2003 server. We use workflow packages that executes childpackages simultaniously. Errors concerning memory and buffers popped up at random in different childpackages.

We installed SP1 and the Cumulative hotfix package (build 2153). This helped a little but didn't solve all the problems. We are testing for two days now and it seems that changing the SSISpackage-property MaxConcurrentExecutables from -1 to 1 in the workflow package solves the remaining memory and buffer problems. Setting this property prohibits concurrent execution of childpackages.

It seems buffermanagement on 64bit servers is still a bit buggy.

Hope this helps in your case too.

Jan

|||

Thanks for the help. I narrowed the problem down to the script components in the child packages so I deleted them and recreated them and it seems to work fine.

No sure why this is. Any one know?

|||

Strange.

Did you create them in a beta version?

Do you have a copy of the old package? Maybe check the differences in the XML in the .dtsx file.

-Jamie

|||

hi,

I also have same problem and also recreate my Task....but not solve my problem

I'm using Script Component Task to do custom transformation.
I add 12 output columns in output0.
5 of these columns is DT_STR and their length is 4, 3 is DT_STR 2, 3 is
DT_STR 12 and 1 is DT_I4.

When executing the package ,I get an exception as below.

The value is too large to fit in the column data area of the buffer.
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32
columnIndex, String value)
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32
columnIndex, Object value)
at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32
ColumnIndex, Object value)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.Output0Buffer.set_isjsst(String
Value)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.ScriptMain.Input0_ProcessInputRow(Input0Buffer
Row)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.Input0_ProcessInput(Input0Buffer
Buffer)
at
ScriptComponent_04dc3685b8d744898f09d88716141989.UserComponent.ProcessInput(Int32
InputID, PipelineBuffer Buffer)
at
Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32
inputID, PipelineBuffer buffer)

I debug and find the execption comes form output0Buffer set value.
I alse do some testing,
1.Create a simple package wiht same Script Component Task and find no
exception.
2.Remove some output0 columns and find no exception.
It's very confusing me...

Any help is very appreciated.

|||I had the same error and exception. I deleted and recreated the script component and the destination component that the output from the script component was going to. It works for me now.|||was getting the same error - found the resolution was to make sure the output column created in the design script was set to the same size as the source column .

In my case I was trying to set the desitination column to 35, since that is the size of the ultimate destination column in the destination database. Even though I was making sure the string inside the script was truncating to 35 characters, the design script component would still throw the error.

I instead create a derived column component immediately following the script component, and use that one to do the trucation to the destination column size...|||In my case it was Custom Component which did not update OutputBuffer field lengths after a change in a Source component

Thanks team for pointing the right direction!