I have 10 Million customer numbers in my data base table. I want to run query every time for first 1 million customers and save that data in .csv file. How can I do that with SSIS package? Ex ,
Select First name, Last name, Customer Id From Customer Where Customer Id between 1 to 1,000,000
Now get the result and store data in test1.csv file on local.
Select First name, Last name, Customer Id From Customer Where Customer Id between 1,000,001 to 2,000,000
Now get the result and store data in test2.csv file on local.<h2>Up to 10th times</h2>
10 million customer number should be dynamic,,It is not fix, next time it may be 20 millions so, we have to run same query 20 times on database.
Here's an algorigthm OTTOMH<ul><li>
Do a rowcount on the table store that into a variable called TotalRows</li> <li>
Divide TotalRows by 20 and store that into a variable called IncrementValue.</li> <li>
Create a FOR LOOP with a variable called RowCounter that goes from from 0 to TotalRows increments by IncrementValue</li> <li>
Create a variable called SelectQuery with evaluate expression set to true, and use the RowCounter and IncrementValue to create the appropriate Select Statement</li> <li>
Create a variable called OutputFileName with evaluate expression set to true, and use the RowCounter and IncrementValue to create a filename</li> <li>
Create a Connection Manager of type FlatFile called OutputFilepath and point it to the desired folder that you want it to write to</li> <li>
In the connection manager's expressions, create a dynamic connection string using the required folder path and the variable FileName</li> <li>
Use a DataFlow inside the for loop and add a OleDB Data Source and a Flat File destination</li> <li>OLEDB Data Source query should be a variable, set it to the variable SelectQuery</li> <li>point the Flat File Destination to the OutputFilepath</li> </ul>
Here you find also good solution <a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/91cdfb63-3368-4049-b3a0-37faeffc4cdb" rel="nofollow">http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/91cdfb63-3368-4049-b3a0-37faeffc4cdb</a>