July 26, 2016 at 1:15 am
The more I read about this the more confused I get.
We keep getting the following error:
Error
Executing the query "exec Ins_activity_table ?, ?, ?, ?" failed with the following error:
"Conversion failed when converting from a character string to uniqueidentifier.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Some additional info:
1)we have changed from having the insert statement in the task to the store procedure below.
2)we do not get this error on the same table every time
3)we only get the error in the deployed environments, but not during debugging in the Bits/SSDT, here we always have a value, thus no null/empy value for the @MID
4)we would prefer not to change the SQLSourceType to Variable as this will result in more than 6000 manual changes in various SSIS projects. (find replace in Notepad++ will not work for this)
---------------------------------
SSIS execute SQL Task Editor
Name = InsertActivityLogBegin
Connectiontype = OLE DB
SQLSourceType = Direct input
SQLStatement = exec Ins_activity_table ?, ?, ?, ?
IsQueryStoreProcedure = False
BypassPrepare = True
Parameter Mapping
User::MIDinputvarchar0
User::TblNameinputvarchar1
User::Containerinputvarchar0
User::Packageinputvarchar0
-----------------------------------
Procedure Ins_activity_table
@MID nvarchar(50), @TabelName nvarchar(225)
,@Contianer nvarchar(225) ,@Package nvarchar(225)
As
Begin
Insert into activity_table
(Activity_ID, MID, Activity_detail, Activity_date)
Values (newid(), convert(uniqueidentifier, @MID), ‘Some descriptive detail related to the activity’, getdate())
end
Thank you
July 26, 2016 at 3:39 am
Wrap the insert inside a try block and inside the catch block insert to a different table with @MID defined as varchar(50). You would at least know what is happening .
July 26, 2016 at 3:56 am
Where do the values from those variables come from? It looks to me as if you're inserting into activity_table one row at a time, which is very inefficient. But that's not directly related to the issue you're having.
The first thing I would try is to make the @MID parameter to the stored procedure char(36). That way, you'll catch any unconvertible values that little bit earlier. The second thing to do is to validate the data upstream. If it comes from a table in SQL Server, run TRY_CONVERT on it. If it comes from a text file or similar, get it into a staging table and then TRY_CONVERT it. Can you see the value(s) that your package is failing on?
John
Edit: better still, make the @MID parameter uniqueidentifier.
July 26, 2016 at 4:22 am
HI John
The MID (which is a GUID linked to specified source table) is retrieved with select statement form a SQL table. As SSIS variable can not be defined as GUID we already had to convert it to a string.
Now we are having problems when we want to do an insert into another table where the column we insert into is defined as GUID so we can link to other tables for reporting.
July 26, 2016 at 4:23 am
HI joeroshan
We are currently busy with this scenario in the test environment.
Thanks
July 26, 2016 at 11:28 pm
HI
we found that the MID have lost its value somewhere and was null in the store procedure. This does not make sense as the rest of the information returned in the same select [from one talbe] is there (also passed to the store procedure to check that the select did work)
we have enough info passed to the store procedure to enable us to now get the GUID in the store procedure in the event that it is empty
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply