Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


In SSIS package Need to Create new table Dynamically for each 1000 Records


In SSIS package Need to Create new table Dynamically for each 1000 Records

Author
Message
Tony1234
Tony1234
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 160
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
rhythmk
rhythmk
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 1062
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
Tony1234
Tony1234
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 160
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.
sqlbi.vvamsi
sqlbi.vvamsi
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 412
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8283 Visits: 14368
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45434 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8283 Visits: 14368
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
Attachments
SSIS_SC.jpg (213 views, 49.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8283 Visits: 14368
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
Attachments
Package5.zip (24 views, 14.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search