81604

Create multiple flat file from running same query with different criteria

Question:

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.

<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.

Thanks, Sanjay

Answer1:

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>

run it

Answer2:

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>

Recommend

  • CSV to JSON in angularjs
  • Loop Through TD Element in HTML document from inside Frame VBA Excel IE8 Automation
  • Global variable doesn't get initialized nodejs
  • How can I inform a user that the value he's entered has been constrained to the JSpinner lower
  • Manage gmail signatures with python service account
  • Ways to hide html5/javascript code [duplicate]
  • Is it possible to change the owner of a closure?
  • Multithread file reading overhead in java
  • Extracting a table row with a particular attribute,using HTMLAGILITY pack
  • Filter data from DataTable
  • Notepad++ - can you use regex to move the last word on a line to the front?
  • Android application update only for one country
  • How to set table heading dynamically in angular 2?
  • Java JComboBox Autocomplete [closed]
  • Convert Razor HTML into downloadable PDF
  • How to sum one column value to other column value
  • Handling asynchronous responses
  • Timezone issue when filtering XTS using .indexhour
  • Datagridview to Clipboard with formatting
  • Can you define a custom collation using a function in XSLT?
  • Python: Cross-platform solution to detect physical non-HT CPUs?
  • Increment inside angular expression
  • How can I handle ArrowKeys and < (Greater Than) in a Javascript function? Which event and which c
  • Why Is My Azure SQL Database Table Permanently Locked?
  • Android - FM/Radio Transmitting
  • QStandardItemModel::removeRows() does not work in my use case
  • Adding a button at the bottom of a table view
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Where to put my custom functions in Wordpress?
  • Weird JavaScript statement, what does it mean?
  • How do you troubleshoot character encoding problems?
  • How to format a variable of double type
  • Buffer size for converting unsigned long to string
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Binding checkboxes to object values in AngularJs
  • How to Embed XSL into XML
  • Android Heatmap on canvas or ImageView
  • Conditional In-Line CSS for IE and Others?