0

I am new to SSIS and creating package for IDL (incremental data load) for multiple tables.

My package has Execute SQL TASK and it is working when I set value of variable from script task But when I try to set value of variable from script task for data flow (oldedb source ) It is not working e.g

My SSIS package:

  • Foreachloop container

  • Script task to loop through schema for table and set SourceTable=TableName.

  • oledb source (use SourceTable)
  • lookup (Lookup no match out put)
  • Oldedb destination

Any idea why??

enter image description here

Update: enter image description here

Improve this question
2
  • Have you specified the variable as Read/Write for Script Task? Commented Aug 16, 2017 at 5:24
  • Yes I did so User::SourceTable in ReadWriteVariables Commented Aug 16, 2017 at 5:42

1 Answer 1

Reset to default
2

SSIS reports such exotic error from Data Source Component when it is unable to extract metadata information (column names and data types) from the source specified. Below are possible cases and ToDos:

  • Data Source is validated before package run, at this time no value at SourceTable variable exists. ToDo - specify DelayValidation=true for DataFlow task, or set some valid default value (pointing to table existing at start time) for the SourceTable variable
  • SourceVariable contains invalid table name at run time. This happens if you made changes described above and the error persists. Use the debugger to inspect its value and analyze the case.
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I used debugger to check values from script task and script task is passing value to dataflow task however I still unable to write to package variable in dataflow task also I set DelayValidation=true. See, like I can only write to package variable value if I use script component.
Now I seem to understand. You are trying to write to a package variable in a script transformation component, right? In this case you might find this helpful - stackoverflow.com/a/39206880 and stackoverflow.com/a/32229160.
Yes you got it. I am using script component and setting value of variable in dataflow task seem like I am missing something as I can't use variable in dataaccess mode of oledb destination e.g check my update in question.
@shaadi, please refer to links provided. Standard MS way restricts you on modifying ReadWrite variables only at PostExecute phase. Another way - use VariableDispenser with care - lock variables at PreExecute, modify at Row or PostExecute, unlock - at PostExecute.
could you please explain me step by step how I can set value to variable in dataflow task at runtime. this would be a great help if you can find a way.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.