SSIS loop iterated package helping

Question

How to append specific data in flatfile destination using SSIS package loop iterate?

Source: emp (SQL Server) sample data. In this table I have 1 million records

I need to load specific empid information into flat file destination using loop iterate.

Sample data

empid | Ename
------+---------
1     | ab
2     | dg
3     | hg

Load the specific empid information maintain in empmaster table.empids values frequenctly changing in empmaster table

Table: Empmaster

Empid
-----
1
2
5
6

Destination: text files emp.txt

empid  | Ename
-------+--------
1      | ab
2      | dg

I tried like this:

Step 1: create 2 variables empid (datatype varchar) and empidresult (datatype object)
Step 2: drag and drop the ExecuteSQL task and using oledb connection and SQL command is: select distinct empdid from empmaster and resultset is: fullresultset and resultset variable select the empidresult
Step 3: drag and drop the foreachloopcontainer and container type is foreachadoenumerator adoobjectsource variable is: empidresult and variable mapping is: empid
Step 4: inside container drag and drop the dataflow task and using oledb source connection SQL statement with parameters
```
select empid,ename from emp where empid =? and  parameter mapping to empid
```
Step 5: drag and drop flatfiledestination and configure emp.txt file here I select the columns names in the first data row unchecked and overwrite data in the file unchecked and executed the package i can append the data for required empids data but header is missing

I tried another methods empmaster records i have converts rows to columns and result store into object with commasepareate buts its supporting only up to 9000 records

if master table have more than 10,000 records then its not working I need first row as header then remain each loop iterate should be data append in destination files.

How to achive this task in sql server?

KeithL KeithL · Accepted Answer · 2020-07-14T13:30:44

what's wrong with a data flow sourced by:

SELECT empid,ename 
FROM emp
   JOIN Empmaster on emp.empid = Empmaster.empid

and then just run that into a flatfile output.

SSIS loop iterated package helping

1 Answers