0

I followed the post from bilinkc as mentioned here

how to load extracted file name into sql server table in SSIS

I use this extracted file name in a variable called FileName in a data task within a For Each container. But when I want to use the same variable in a consecutive Execute SQL task it is throwing up the error

[Execute SQL Task] Error: Executing the query "

INSERT INTO HOURLY_DATA ..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Parameter settings : Data Type is VARCHAR Parameter name is 0 and Parameter length is 1000.

Is there anyway to get around this error. WHen i checked on other posts they say it is related to a TEXT length error but I am not able to get my head around this solution for my scenario.

The full query is as given below:

This is the full query :

INSERT INTO HOURLY_DATA (UNIQUE_NUM, NAME, FILE_NAME, USER_NAME) SELECT DISTINCT UNIQUE_NUM, 'MANUS',?, 'MENON' FROM MY_TABLE WHERE UNIQUE_NUM IS NOT NULL

For the parameter variable, I have a package level variable defined which depends upon this expression:

REPLACE(REPLACE(REPLACE(REPLACE(@[User::varFilePath],@[User::varSourceFolder],""),"\",""),"My File",""),".xlsx","")

But when I evaluate this expression nothing happens. Could this variable be the issue?

Thank you in advance for your time and help.

Community
  • 1
  • 1
Manus
  • 869
  • 2
  • 10
  • 20
  • What is the query in the Execute SQL task? Are there any other OLEDB messages before or after the error message in the output? – Tab Alleman Apr 14 '16 at 18:01
  • I have put the full query up in the main question now Tab – Manus Apr 14 '16 at 18:13
  • Apologies bilinkc. I have updated the question with the data points Tab requested for. Could you give some feedback based on the additional data now. Your posts have always been helpful :-) – Manus Apr 14 '16 at 18:19
  • 1
    If the expression is evaluating to nothing, that could be a problem. I suggest populating a variable with your entire SQL string (build using expressions) and use the "SQL From Variable" option in Execute SQL instead of parameterizing your query. Put a breakpoint in the pre-execute and look at the value of the variable to see if it contains the query you expect it to. – Tab Alleman Apr 14 '16 at 18:25
  • Hello Tab, your suggestion worked. Thanks a ton!! – Manus Apr 15 '16 at 15:21
  • That's good to hear, but did you ever figure out why the expression was evaluating to nothing, or why the parameterized query didn't work? – Tab Alleman Apr 15 '16 at 15:32
  • Oh yes. it was a silly mistake. I was not using double quotes to get the expression. I had copied it from my derived expression box and forgot to adapt it to the expression builder format. – Manus Apr 15 '16 at 15:33
  • Ah, in that case, we should just chalk this one up to a typo, I think. – Tab Alleman Apr 15 '16 at 15:35
  • Yes, I believe that is all there was to this – Manus Apr 15 '16 at 15:39

0 Answers0