December 8, 2011 at 9:38 am
Hello All,
I am having issues using the foreach loop control item in SSIS. I have created a job that populates a table with appointment details of staff over a set period. I want to go through the table and get the entries for individual staff, write them to excel and then email them to each staff.
My problem is that when I try to do a foreach loop in SSIS to get individual staff appointment details from the table and write to excel it keels over and dies on the foreach loop.
I have sucessfully written the monthly appointment table into a variable (by creating a data flow task on the control flow pane and then within the task setting up a OLE DB Source and RecordSet destination where I write it into a variable of type object). This bit runs sucessfully when launched.
Next I created a Foreach loop (containing a data flow task) with the variable containing the appointment details from above as the ADO object source variable and with a new string variable in the variable mapping pane. The Foreach loop data flow task contains a source and a flat file destination (I tried to use an excel destination initially) but neither approach has worked.
I get errors saying:
Error: Variable "User::****" does not contain a valid data object.
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.
The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
When I initially tried to write to xls i got the following error:
[Connection manager "SQLSERV02.Roster2000SQL"] Error: The connection string format is not valid.
It must consist of one or more components of the form X=Y, separated by semicolons.
This error occurs when a connection string with zero components is set on database connection manager.
[Excel Destination [82]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "input "Excel Destination Input" (93)" failed because error code 0xC020907B occurred,
and the error row disposition on "input "Excel Destination Input" (93)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
Many thanks for your help.
December 8, 2011 at 5:17 pm
Have you confirmed that the Object variable contains data when the Foreach Loop runs? I can reproduce the same error when the Object variable is empty.
Also, what source component are you using in the Data Flow task inside the Foreach Loop?
December 9, 2011 at 5:35 am
Thanks Sabotta, it all works fine now.
I am stumped on another issue though ... If I want to write individuals appointments to indivdual spreadsheets for emailing later on how do i do that? (i.e. I want a spreadsheet for each staff containing their appointments only) At the moment it copies all appointments for all staff from the table into one excel sheet when I use excel destination in the foreach loop and copies all appontments into different files named for each staff when i use the file destination.
December 12, 2011 at 1:17 pm
You can use an expression on the Excel Connection Manager ExcelFilePath property, to control the file name. You can then write to a different file on each loop iteration.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply