May 1, 2019 at 10:07 pm
Here is the scenario:
I have a Script task which has C# code and it returns a json string and I am storing the string value into a SSIS variable.
I am passing this SSIS variable to a Execute sql task and Parse those varaible values into sql server destination.
I am not able to understand why my variable is not working when it is passed to Execute sql task?
Response is the SSIS variable(String).The below script I am using in a Execute sql task.
CREATE TABLE Response22
(
msg VARCHAR(100)
, Responsedata VARCHAR(MAX)
, FoundCount int
, TotalCount int
)
DECLARE @Response VARCHAR(MAX)
SELECT Response.*
INTO Response22
FROM
OPENJSON(@Response)
WITH
(
msg VARCHAR(100)
, Responsedata VARCHAR(MAX)
, FoundCount int
, TotalCount int
)AS Response
[Execute SQL Task] Error: Executing the query "CREATE TABLE Response22
..." failed with the following error: "There is already an object named 'Response22' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have no idea how to resolve the issue Any feedback or alternative approach is helpful.
Thanks
May 2, 2019 at 3:49 pm
It depends partially on your type of connection how you're supposed to access variables within SSIS.
May 2, 2019 at 4:07 pm
Try adding
DROP TABLE IF EXISTS Response22;
before your CREATE TABLE statement.
Or change your CREATE TABLE statement to
TRUNCATE TABLE Response22;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2019 at 7:20 pm
.. or using
I hate using truncate/deletes.. granted .. not sure if your account/service account will have access to sys tables
IF NOT EXISTS (SELECT name FROM database.sys.tables where name ='Response22')
BEGIN
CREATE TABLE Response22
(
msg VARCHAR(100)
, Responsedata VARCHAR(MAX)
, FoundCount int
, TotalCount int
)
END
May 6, 2019 at 1:43 pm
.. or using I hate using truncate/deletes.. granted .. not sure if your account/service account will have access to sys tables
---
Please describe the problems you believe TRUNCATE introduces which are not present with DROP/CREATE.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2019 at 4:20 pm
It just depends on the nature of the table.. if I can avoid deleting data I prefer to do it another way, whether that is checking if the table already exists or verifying if the record already exists in the table or not(and is unchanged).. which granted.. may be less efficient but is safer imo. If it were a temp table then I wouldn't have an issue with truncating.
May 7, 2019 at 7:30 pm
It just depends on the nature of the table.. if I can avoid deleting data I prefer to do it another way, whether that is checking if the table already exists or verifying if the record already exists in the table or not(and is unchanged).. which granted.. may be less efficient but is safer imo. If it were a temp table then I wouldn't have an issue with truncating.
You are right to be concerned about truncating a table which might contain data which you don't want to lose! I took that as read.
I usually put TRUNCATE/LOAD type tables in their own separate schema, to make it clear that the data which they contain is transient.
What I do not like about your CREATE TABLE method is that your database will be (at least some of the time) out of line with what is contained in your VCS. IMO, permanent tables should be neither created, amended nor destroyed within stored procs without good reason, because doing so potentially introduces drift.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply