SSIS Get data from a Flat file and assign to varia

问题:

In SSIS, i want to read data from a flat file(Flat File Source) and assign certain column values to a variable in SSIS (like IDNo). I need this ID to retrive other information like email and other details to send email dynamically later in the process. I did some reasearch i found that Script Component is one of the way to assign values to variables, but in our environment script component is not available. Is it possible to do that if so, can some one please provide me a sample. thanks.

回答1:

You don't necessarily need script component to achieve this.

1. Read your IDs to a recordset (that would be a variable of type Object):

   • Create a variable of type Object.
   • In Data Flow Task create a Recordset Destination.
   • Pick your variable in VariableName dropdown.

2. Create Foreach Loop Containter:

   • Choose Foreach ADO Enumerator in Enumerator setting.
   • As ADO object source variable pick your recordset variable from the dropdown list
   • In Variable Mappings assign your email variable with index 0

3. Send the emails from the loop using email variable as an expression in Send Email Task.

Why is script component not available? It'd make things easier and help to avoid some problems; I don't use mail task anymore in favour of script task, because I ran to some problems with it in the past.

Hope this helps.