December 30, 2008 at 11:11 am
hi,
I have a stored procedure that inserts some values into a table, using contactid as parameter. here is the code:
delcare @ID;
set @ID = @@identity;
insert into C (Guid, ID) values (@Guid, @ID);
if i run this manually and give an id it works fine, but if i ran it through ssis (i have execute sql task and it has ole db connection and i have exec procedurename ? and i pass the conactid )... it says Error: Cannot insert the value NULL into column 'ID', table 'dbo.C'; column does not allow nulls. INSERT fails.
HELP PLEASE!!!!!!!!!!
December 30, 2008 at 11:27 am
set @ID = @@identity;
This is your problem.
Look up @@identity in books online.
It will be empty unless you have just inserted into a table with an identity column.
It is there to return the identity value for a record that you just inserted.
You would normally see it used:
[font="Courier New"]INSERT MyTable (Descr) VALUES ('MyRec')
SET @MyID = @@Identity --Or @@Scope_Identity (usually)[/font]
December 30, 2008 at 11:37 am
Are you trying to autogenerate a number during your insertion or pull the last identity field that was in that particular table?
These are two different tasks and require two different sets of code, so let us know what you're trying to accomplish and we'll try to help.
December 31, 2008 at 2:27 am
basically the id is already in other table and is foreign key in the C table. all i want is to have same id as my parent table i have tried this but still fails:
set @ID = @@identity;
select @id = id
from cs
where contactid = @contactid
December 31, 2008 at 3:18 am
Hi Guys,
the above code worked. i am trying to get the contactid in object then convert to int32. but if for instance the package fails then it keeps on failing, unless i change the datatype of contactid and run it again.
is there a work around this???
December 31, 2008 at 4:38 am
Your problem suddenly makes no sense to me.
If I understand, you have TableA which has an PK identity field. Then you have TableB where you're trying to take the TableA.PK and insert it into TableB, but what about the rest of the data from TableA?
Are you only inserting TableA's PK into TableB or are you inserting other stuff into TableB? If the later, where is the other stuff coming from? If the former, why do you need variables?
Please post the structure of both tables and examples of the data you're trying to insert plus examples of the relevant "source" data and give us a comprehensive list of any conditions you might be checking. Without this information, we're working in a vacuum and can't help you properly.
December 31, 2008 at 5:07 am
Ok so let me clarify. i think i have found the error.
i am getting the contactid using a sql statement from another table and passing that as object in ssis then loop the object using ado enumerator to get contactid in int32 variable.
sometimes this works fine but sometimes it doesnt get the contactid. that is why it says null cannot be inserted....
i have got a script to show me the contactid in msgbox this sometimes displays the contactid but most of the times doesnt.
so i am guessing this is my problem. Is there any other way to get multiple contactids and convert them to int or varchar as my stored proceedure uses int as parameter?
December 31, 2008 at 5:33 am
afgone,
I still don't understand why you need to do all this fancy work for one field.
If you're just trying to change the data type of this field, use a Derived Column conversion between source and destination.
There's no need for looping or ado enumerators unless you're doing something else that is complicated. And if you are doing something else, we need the explanation of what else you're doing so we can help you appropriately. Broad picture kind of explanation.
December 31, 2008 at 7:53 am
it is quite complicated package i need to send emails using contactids. so i have got a query to select these contact ids and assign them to an object variable then i convert this object into int using loop and send emails. but when i think either the object creation or the loop doesnt work sometimes it does and sometimes not.
hope that is clear enough
December 31, 2008 at 8:02 am
Aha! That's the detail that was missing. @=)
Give me a few. I think we have a package that does something similar, but we use a WebService, so I'm not sure how that differentiates from what you're doing.
December 31, 2008 at 8:07 am
Okay, here's what we've got.
For-Each Loop with an ADO Enumerator. The configuration is set to an Object variable (as you said) with the mode as Rows in the First Table. Variable Mappings are set to another variable (MyID) which is Int32.
So far it sounds like we're doing the same thing. Question, what datatype in the DB is ContactID?
Anyway, we have a Execute SQL Task which runs a stored procedure. This proc updates tables in the database.
After the Execute task is a Script task calling a function. However, I think you'd be fine with using the Execute Task to run DBMail stored procedures without needing the additional Script Task.
December 31, 2008 at 8:34 am
that is exactly what i am doing. contactid is int in database.
the email part works fine after getting this object i am inserting some data to a table as my first post. this is where it falls apart. i think the object doesnt gets set. how do you set your loop and execute sql task part...
December 31, 2008 at 8:42 am
The Execute SQL Task takes in a parameter in the stored procedure.
Goto Parameter Mapping on the left side menu. Enter in the variable name and call it 0 in the Parameter Name column. Direction should be Input. Make sure the Data Type there is set to LONG.
Then in the General screen, in SQL Statement, you have your stored Procedure called as "Exec dbo.MyStoredProc ?"
The ? is used as a parameter placeholder. It'll automatically send in the variable.
All Parameter Names should be numbers, BTW, starting at Zero and working their way up in order. Then, the question marks will take those parameters in order. So if you're using a multi parameter proc, the first ? will read parameter 0, the second will read parameter 1, etc.
And you'll want to write a stored proc to wrap around the DB mail proc so you can properly pass in your parameters. But that's more a T-SQL forum question if you're unsure of that.
January 1, 2009 at 11:23 pm
sorry for late reply mate and thanks for your answer.
so if i have int as my contactid in db and storedprocedure should i pass this as long to exeecute sql task?
January 2, 2009 at 4:53 am
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply