Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

In SSIS package Need to Create new table Dynamically for each 1000 Records Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 10:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:19 AM
Points: 18, Visits: 140
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
Post #1359033
Posted Thursday, September 13, 2012 11:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 397, Visits: 614
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/
Post #1359043
Posted Thursday, September 13, 2012 11:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 4:19 AM
Points: 18, Visits: 140
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.
Post #1359051
Posted Sunday, September 16, 2012 9:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 54, Visits: 332
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
Post #1359956
Posted Thursday, September 20, 2012 11:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1362184
Posted Thursday, September 20, 2012 4:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1362366
Posted Thursday, September 20, 2012 4:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy


  Post Attachments 
SSIS_SC.jpg (126 views, 49.33 KB)
Post #1362371
Posted Thursday, September 20, 2012 4:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy


  Post Attachments 
Package5.zip (9 views, 14.15 KB)
Post #1362373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse