In SSIS package Need to Create new table Dynamically for each 1000 Records
-
Dear all,
I had a requirement like, I have a flat file with 10,000 records, I need to load this file to the Sql server tables for each 1000 records I need to create new table.
ex: for this file I need to create 10 table to load the 10,000 records.
Please help me on this.
Thank'q
-
venkatesh.b 88975 (9/13/2012)
Dear all,I had a requirement like, I have a flat file with 10,000 records, I need to load this file to the Sql server tables for each 1000 records I need to create new table.
ex: for this file I need to create 10 table to load the 10,000 records.
Please help me on this.
Thank'q
Please let us know how did you approach so far and problem faced by you in implementing that.
However just want to know why SSIS only means you could have bulk import also.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂 -
Thank's for your reply. I haven't stated any approch till now. I know we can do it from BCP. But I need to do this from SSIS package only.
-
can you elaborate more on what you require. why can't we create tables before hand? if they are to be created for every 10K, then what is the structure of tables(same or different for all tables) and what should be names of them
-
You could do this in a Script Component in a Data Flow setup as a Destination. Keep a counter in your code and issue a new CREATE TABLE at 0 rows and then each time you reach 999 rows and direct the rows into the new table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (9/20/2012)
You could do this in a Script Component in a Data Flow setup as a Destination. Keep a counter in your code and issue a new CREATE TABLE at 0 rows and then each time you reach 999 rows and direct the rows into the new table.What kind of "script" would you use for this? Any chance of an example?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/20/2012)
opc.three (9/20/2012)
You could do this in a Script Component in a Data Flow setup as a Destination. Keep a counter in your code and issue a new CREATE TABLE at 0 rows and then each time you reach 999 rows and direct the rows into the new table.What kind of "script" would you use for this? Any chance of an example?
There are two components (generic) we can use in SSIS to add scripting, a Script Task and a Script Component. The Script Component exists within a Data Flow and can act as a Data Source, a Transformation somewhere between a Source and Destination, or a Destination. In this case we would add a Script Component and choose to configure it as a Destination meaning it would take a data input and act as the endpoint of that SSIS pipeline meant to result in the application of that data to a database or some other data store.
As for the package itself it might look something like this:
And here is some pseudo code for the Script Component:
/* Microsoft SQL Server Integration Services Script Component* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region added by opc.three
private int rowCount;
private string destinationTableName;
#endregion
public override void PreExecute()
{
base.PreExecute();
#region added by opc.three
rowCount = 0;
#endregion
}
public override void PostExecute()
{
base.PostExecute();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
#region added by opc.three
if (rowCount % 1000 == 0)
{
// todo: create a new table and store name in destinationTableName
}
// todo: insert row into destinationTableName
// increment counter
rowCount += 1;
#endregion
}
}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Adding sample package (SQL 2008 R2) as attachment in case there is interest.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply