SSIS Execute SQL Task contain Insert Into and Select

Question

I am attempting to read file names then input each name in a row with another similar cell value to another table column. Within the For Each Loop, then I have the Execute SQL Task. currently the Task is set to Direct Input. This is the problem script

INSERT INTO TableB
                  (LoopValue, Columnvalue)
VALUES     (?, N'Select Columnvalue from TableA where Columnvalue like ?')

The expectation is that you'll get something other than the literal string `Select Columnvalue from TableA where Columnvalue like ?` stored in Columnvalue? Is that the problem? — billinkc, Oct 27 '20 at 01:10
is this a subselect or do you want to actionally insert the sql script text? "Select Columnvalue from TableA where Columnvalue like ?" if it is subselect change ' to ( — KeithL, Oct 27 '20 at 10:22
I encountered this error message [Execute SQL Task] Error: Executing the query "INSERT INTO TableB (LoopValue, Columnvalue)" failed with the following error: "Incorrect syntax near ')'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. >>>> I want to insert the Select values into the table — Samuel, Oct 27 '20 at 10:23
I also suggest getting your query to work in SSMS first. If you have trouble then converting, then post the SSMS query that workewd and we can help you convert it — KeithL, Oct 27 '20 at 10:25
what does the resulting data set look like? Please post an example — KeithL, Oct 27 '20 at 10:29
Please advice where to find the resulting data set. On the Execute SQL Task, the ResultSet is None — Samuel, Oct 27 '20 at 10:44
Testing the script in SSMS showed there is problem passing the LIKE variable >> INSERT INTO TableB (LoopValue, Columnvalue) VALUES ('Apple_For_Lunch - breakfast earlier', N'Select Columnvalue from TableA where Columnvalue like 'Apple_For_Lunch - breakfast earlier'') — Samuel, Oct 27 '20 at 11:16
do you want the result of that select or the words "select ..."? — KeithL, Oct 27 '20 at 11:24
you are also using a like operator without a wild card. Just use = — KeithL, Oct 27 '20 at 11:25

score 0 · Answer 1 · answered Oct 27 '20 at 12:05

You have a for each loop over files, as I understood it? When I have these kind of situations I simply map the current file to a fileName variable using the "Variable Mapping" setting of the foreach loop, using index 0.

Then I create a Data Flow task with a derived column which is simply your fileName variable. Using this in conjunction with an OLE DB destination ought to be sufficient to insert the value into a table, if I understand you correct?

EDIT: Oh you want to create a string as well? You could achieve that in your Control Flow using an Expression Task. Assign a string variable, such as "queryString" and then have:

@queryString = "Select " + @[User::fileName] + " from Table B"

or something in the lines of that, i.e. use "" to insert "hard coded" values and a + followed by your variable to concatenate your variable. You could then also use a derived column for your @queryString variable in your data flow as well.

score 0 · Answer 2 · answered Oct 27 '20 at 13:47

You're close if I understand what you are attempting to do which is insert into TableB selecting records from TableA where ColumnValue is like the passed in value.

The way you have it now it wants to insert the literal string of Select Columnvalue from TableA where Columnvalue like ? into your table.

Tweak your statement like this:

INSERT INTO TableB (LoopValue, Columnvalue)
SELECT ?, Columnvalue  FROM TableA WHERE Columnvalue LIKE '%' + ? + '%' --% is a wildcard.   If you only want those that begin or end then remove one or the other %

Then make sure you're passing in the value twice. Since there are multiple ? that relates to the number of parameters, but you can pass it twice since you are using the same value in two different places:

Parameter names would then start with 0 and increment up to match the ? in your statement. Depending on your connection type that could be different, there is a section in the following documentation that tells you what it should be Execute SQL Task

SSIS Execute SQL Task contain Insert Into and Select

2 Answers2