CODEWITHSUNDEEP
ssis
Sanjay
Sanjay

Reputation: 41

Create multiple flat file from running same query with different criteria

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 ,

First time:

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.

Second time:

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.

Up to 10th times

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.

Thanks, Sanjay

Upvotes: 3

Views: 6626

Answers (2)

Sanjay
Sanjay

Reputation: 41

Here you find also good solution http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/91cdfb63-3368-4049-b3a0-37faeffc4cdb

Upvotes: 1

Raj More
Raj More

Reputation: 48016

Here's an algorigthm OTTOMH

  • Do a rowcount on the table store that into a variable called TotalRows

  • Divide TotalRows by 20 and store that into a variable called IncrementValue.

  • Create a FOR LOOP with a variable called RowCounter that goes from from 0 to TotalRows increments by IncrementValue

  • Create a variable called SelectQuery with evaluate expression set to true, and use the RowCounter and IncrementValue to create the appropriate Select Statement

  • Create a variable called OutputFileName with evaluate expression set to true, and use the RowCounter and IncrementValue to create a filename

  • Create a Connection Manager of type FlatFile called OutputFilepath and point it to the desired folder that you want it to write to

  • In the connection manager's expressions, create a dynamic connection string using the required folder path and the variable FileName

  • Use a DataFlow inside the for loop and add a OleDB Data Source and a Flat File destination

  • OLEDB Data Source query should be a variable, set it to the variable SelectQuery
  • point the Flat File Destination to the OutputFilepath

run it

Upvotes: 1

Related Questions