Hi There,
Some one please help me to achieve this task.
I have task to join 2 tables and insert values.The table which i am inserting values has typeid column which is primary key column.I supposed to insert values except this column(TypeId).When i m trying insert values its throw an error saying
Error: Cannot insert the value NULL into column column does not allow nulls. INSERT fails.
Please let me know ther is a way to set duplicate id for this rows?
Thanks in advance.
Don't select the typeID column in your OLE DB destination.|||I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?|||
Rafael Salas wrote:
I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?
It could either be an identity, or he needs to assign a value to it.|||
Hi ,
Yes i want to assign a value automatically for the TypeId for inserting rows.
|||See if this helps you. Take a look at my post:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211015&SiteID=1|||
Hi Phil,
Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?
|||
Hi Phil,
Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?
I wrote one just for you... http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/
Also, a more simplified version can be found at SQLIS.com. It does not start where the table left off. (It assumes starting at 1 always). http://www.sqlis.com/37.aspx
|||
Hi Phil,
I have gone thru your articles i got the ideas to do. But i am facing problem while using theis scripts.
i have no.of sql statements i have droped no.of execute sql tasks for each statements.Please find the below script,
select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType
__
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in
' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If
Row.TypeId = NextKey ' Assign NextKey to our ClientKey field on our data row
End Sub
End Class
I followe all your steps as mentioned.Please Advice me.
|||Does your query return more than one row? Notice that you aren't selecting the max(typeid) from the table, tblType.
select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType|||
Hi Phil,
Yes you are right.It returns more than one row.Please advice how to achieve this?
Please correct the script if there is a problem.
Many thanks in advance.
|||
Hi Phil,
Yes you are right.It returns more than one row.Please advice how to achieve this?
Please correct the script if there is a problem.
Many thanks in advance.
Change your query to select the max(typeid) from the table. Your query isn't the same as mine.
