Anyone is aware of this SSIS Error.

Hi Folks,

I developed my ssis package against my local database and i would like to test it against remote server database.For that i have modified all of my connection manager settings still i am getting error on oledb destination and Execute T-Sql task.The below is the error.

[OLE DB Destination [11665]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK__tblPr__532FC765'. Cannot insert duplicate key in object 'dbo.tblPr'.".

Can anyone suggest me what would be the problem and how to resolve this.

Thanks in Advance.

Violation of PRIMARY KEY constraint 'PK__tblPr__532FC765'. Cannot insert duplicate key in object 'dbo.tblPr'.

This means you are trying to insert a key which is already in the table tblPr. Primary Keys have to be unique.. to identify a specific row.

for example (ID is your Primary Key):

ID | SomeColumns
1 | test
2 | testasdf

and now you are trying to insert

1 | anothertest

the column ID (which is your primary key) has already a row with 1 so you can't insert a second row with ID 1

this is just an explanation of your problem .. but i can't help you without any further information... maybe you can fix it on your own when you understand it

- paul
|||

Hi Paul,

I made the changes It works now.Thanks.

I am getting one more error when i am executing Execute T-sql Task.I am using identity column in my tsql statement.

Below is the error.

"User 'guest' does not have permission to run DBCC CHECKIDENT for object '#TempPr__000000000ACC'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please let me know what is wrong if you know..

Thanks so much.

|||

A search on the Internet will yield very helpful results. This is a permissions issue, just as the error says.

Read up on DBCC CHECKIDENT.

http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Anyone here know how to work around the persistent long lines bug?

There is a problem that was at least in the February CTP and the April CTP, that a package will enter a state of being slow to open and always put up a message box saying
(the message box does not allow copying the text, or I would post it verbatim, but it is something about extremely long lines of text, and that the product will run slowly)

I've asked in the newsgroups if there is a file that I could edit, manually inserting carriage returns to try to hack around this bug, but never got an answer there, so I thought I'd try here. (Actually, I think I was told that the bug was fixed, but it has persisted here into the April CTP.)
Does anyone here know a way to fix or hack around this bug, or have tried editing some file (dtsx, dtproj, sln, suo, I don't know) to fix it?

Microsoft Visual Basic 2005 55603-000-0000016-00403
Microsoft Visual Basic 2005
Microsoft Visual C# 2005 55603-000-0000016-00403
Microsoft Visual C# 2005
Microsoft Visual C++ 2005 55603-000-0000016-00403
Microsoft Visual C++ 2005
Microsoft Visual J# 2005 55603-000-0000016-00403
Microsoft Visual J# 2005
Microsoft Visual Studio Tools for Office 55603-000-0000016-00403
Microsoft Visual Studio Tools for the Microsoft Office System
Microsoft Visual Web Developer 2005 55603-000-0000016-00403
Microsoft Visual Web Developer 2005
Microsoft SQL Server Analysis Services Designer
Microsoft SQL Server Analysis Services Designer
Version 9.00.1116.00
Microsoft SQL Server Integration Services Designer
Microsoft SQL Server Integration Services Designer
Version 9.00.1116.00
Microsoft SQL Server Report Designer
Microsoft SQL Server Report Designer
Version 9.00.1116.00
Microsoft SQL Server Report Model Designer
Microsoft SQL Server Report Model Designer
Version 9.00.1116.00

Hi Perry,

You are right; this problem should be fixed in the latest builds (June CTP). The file that complained about long lines is dtsx file. I would not recommend going in and inserting new lines in this file, it could create a bigger mess.

The best thing for fixing it would be to install June CTP build, load the package and save it again using the new build.
Thanks,

anyone good with Integration services

Hi I have a simple package I am putting together. The package runs a query
on an SQL database (database1) and the results are then copied over to
another SLQ database (database2). I have this set up and it works. I am
trying to add another element that will update a table in database1 as long
as the transfer is successful. I also have a script in the control flow that
writes out a message, success! and this seems to work.
Anyhow not quite sure how what to add to update database1 if the transfer is
successful--
thanks.
Paul G
Software engineer.On Oct 31, 11:44 am, Paul <P...@.discussions.microsoft.com> wrote:
> Hi I have a simple package I am putting together. The package runs a query
> on an SQL database (database1) and the results are then copied over to
> another SLQ database (database2). I have this set up and it works. I am
> trying to add another element that will update a table in database1 as long
> as the transfer is successful. I also have a script in the control flow that
> writes out a message, success! and this seems to work.
> Anyhow not quite sure how what to add to update database1 if the transfer is
> successful--
> thanks.
> Paul G
> Software engineer.
Try Adding a Execute SQL Task as control flow and define your update
Query in the task. change the precedence to success beoe this task.|||Hi thanks that seemed to do the trick. I currently a script task on success
and on failure well that writes out a corresponding message. I was wondering
if you know if there is a way to include the number of records updated from
one of the tasks in the message? It would have to be retreived in the
script? Also are the event handlers for any event in a control flow?
--
Paul G
Software engineer.
"Maninder" wrote:
> On Oct 31, 11:44 am, Paul <P...@.discussions.microsoft.com> wrote:
> > Hi I have a simple package I am putting together. The package runs a query
> > on an SQL database (database1) and the results are then copied over to
> > another SLQ database (database2). I have this set up and it works. I am
> > trying to add another element that will update a table in database1 as long
> > as the transfer is successful. I also have a script in the control flow that
> > writes out a message, success! and this seems to work.
> > Anyhow not quite sure how what to add to update database1 if the transfer is
> > successful--
> > thanks.
> > Paul G
> > Software engineer.
> Try Adding a Execute SQL Task as control flow and define your update
> Query in the task. change the precedence to success beoe this task.
>

Anyone else have errors when checking out SSIS package from SourceSafe or TFS?

I get errors when I check out an SSIS package from source control (both Source Safe and TFS) relating to the connection objects where I was not the original developer who checked it in. Is there a solution to this other than altering the connection login\password for every connection object in the package before deploying?

Yes, I do have problems as well when updating CVS.

For SSIS the solution is to use configuration files for your connections and Security / Protection Level property set to "DontSaveSensitive".

For Reporting services and cubes I am still a bit confused.

For reports, As soon as I open them in BIDS, I immediately edit the shared data sources to retype in the account and password. If I do not do it and go to the data page, it will loose all stored procedures parameters :-(

For Cubes, I also revisit the data sources as soon as I open the cube. Not doing so will let you work on your cube and deploy it, however you will not be able to browse, aggregate or process it.

Philippe

|||

The critical item here is to not have the Package Protection Level Property set to Encrypt[Sensitive or All]WithUserKey. That uses the developer's Windows login to encrypt information in the package, so no other developer will be able to work with it very easily, unless they log in with the same Windows account.

As Phillippe said, a common workaround is using a ProtectionLevel of "DontSaveSensitive" and configurations, which also makes it easier to deploy the packages in multiple environments. You could also use "EncryptSensitiveWithPassword" which will mean you have to enter a password for the package before running it, but it will be able to store the passwords inside the package, rather than depending on configurations. The best choice depends on your scenario.

Any way to call a Package from a Script Component?

Just wondering if it's possible to call a package from within a script component. I'd think so, but not quite sure how to.

Thanks,

Jeff Tolman
E&M Electric

Do you mean you want to call a package for each input row?

There is no built-in support for this, but you can execute package using DTEXEC.EXE utility. Use System.Diagnostics.Process class to start the process.|||

Yes, after a row is processed I'd like to run another package. The DTEXEC.EXE utility probably would not work since it would be run in another thread space and there probably would be no way to monitor when that process completed.

The SSIS Script component (VSA) editor doesn't seem to make it easy to reuse pieces of code between packages, otherwise I wouldn't need to call a package. I read in the Help files that if you need to use code across packages then it would best to create a user component, but unfortunately I don't have the time to learn how to do that.

Thanks for your help Michael.

Jeff

|||

Check out sample @. http://mystutter.blogspot.com/2006/03/ssis-2005-returning-values-to-calling.html

The author calls another DTSX package from the script task component.

Thanks,
Loonysan