Still Struggling With Flat File Into Multiple Tables

Jan 23, 2007

So here's the issue

16 flat files all fixed width. Some over 350 columns.

Open flat file 1

extract id and go see if its in table 1, if true update table 1 with first 30 columns

otherwise insert into table 1 first 30 columns.

goto table 2, lookup id, insert/update next 30 columns...etc..etc..for 10 different tables

So I've got my flat file source, I do a derived column to convert the dates, i've got a lookup for table 1, then 2 ole db commands, 1 for update if lookup successful, 1 for insert if lookup fails.

How can I pass the id as a param into the update command so it updates where x = 'x'

also I need a pointer on doing the next lookup, eg table 2, would I do this as some sort of loop?.

If you can help great, but, please don't just reply with "I'd use this object"...then no explanation of how

v.v.frustrated newbie to SSIS

View 8 Replies


ADVERTISEMENT

Struggling To Import Data From Flat File To Sql Db

Apr 1, 2008

Hello all,

We have been trying now for the past 2 days to import data from a flat file to sql server database but with no luck.

The real issue here is that one of the field names has a very long value.

As a result, the import fails because it is unable to truncate the value.

We really don't want the value truncated but we have not been able to import the entire data file.

We have used nvarchar(max) but it doesn't work.

Can someone please let me know if you have encountered this type of issue and how was it resolved?

Thanks in advance.

View 12 Replies View Related

Flat File Into Multiple SQL 2005 Tables.

Jan 17, 2007

Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes,

we have 16 .txt files, some with over 350 columns.

That info from each individual file needs importing to multiple sql tables.

need to look at sql table1 does record exist? if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns...

then look at sql table2 and repeat for next n columns....

So I was wondering is it going to be better to write this as a dtsx package? if so can you point me to an example

or should I just write the code as part of a code behind page that scrapes the info and does a standard update/insert procedure?

Any help would be welcome.

thanks

View 3 Replies View Related

Multiple Flat File Import To Corresponding SQL Server Tables

Mar 23, 2007



We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.

Thank you very much,

Regards,

Srinivas Raveendra

View 10 Replies View Related

Combining Multiple Tables Into A Single Flat File Destination

Aug 21, 2007

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.


e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

View 9 Replies View Related

Writing Data From Multiple Tables To A Single Flat File

Sep 13, 2005

I have a package that contains three database tables (Header, detail and trailer record) each table is connected via a OLE DB source in SSIS. Each table varies in the amount of colums it holds and niether of the tables have the same field names. I need to transfer all data, from each table, in order, to a flat file destination.

View 6 Replies View Related

Importing Multiple Flat Files To Multiple Tables In SSIS

Jun 27, 2006

I have a couple of hundred flat files to import into database tables using SSIS.

The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.

However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.

Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.

I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?

View 9 Replies View Related

Export Multiple Tables To Multiple Flat Files

Nov 29, 2007

I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.

Creating additional sources is fairly easy copy of the first source and change to the table name.

I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.

How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?


This seems like a simple / common task, but I've been unable to find a solution.

Thanks, Richard

View 1 Replies View Related

Multiple Flat Files To Multiple Tables

Jun 1, 2007

Hi,



I have searched but not found quite the best way to look at this so far..



I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.



In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.



Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?



Just wondering if there is a neater way of doing this??



It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.



Hope that explains ok and thanks in advance.



Mike

View 3 Replies View Related

Multiple Flat Files To Different Tables (SSIS)

Feb 21, 2012

So we are running SQL 2000 and we use a DTS Package to load about 100 txt files to different tables in a SQL DB.

So in DTS packages you define the connection (database) and the different source files will connect to the connection.

You define the destination on the flow arrow. My question is how do i accomplish this in SQL 2008 using SSIS. (upgrading SQL).

View 9 Replies View Related

Exporting Multiple Tables As Flat Files

Sep 20, 2006

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

View 3 Replies View Related

Integration Services :: Loading Multiple Flat Files Into Different Tables Using SSIS?

Oct 25, 2015

I have been tasked to do the following using SSIS.

We received two csv files each week and we would like to load these files to two different sql server tables using SSIS.

These files should be archived into a folder after each load.  

How can I achieve this?

View 6 Replies View Related

Populating 2 Tables Using Flat File

Dec 12, 2005

Hi!

I'm trying to setup a DTS that reads a flat file uses a Data Driven Query task and then selects ONLY records that does not exist in the database and then INSERT them to DB1.

This works fine but I need to add another functionality.

I need to create a record on another table(DB2) based on the freshly inserted records in DB1 using only some of the fields. How do I do it?

Is setting up a trigger possible so that everytime a record is inerted in DB1 it will automatically a populate DB2?

Flat file:
ID
Name
Phone

DB1
ID
Name
Phone

DB2
ID
PHone
Event (from 00 to 10)
NumActions (initialized to 0)

Please help.

Thanks.


$3.99/yr .COM!
http://www.greatdomains4less.com

View 2 Replies View Related

Vendor Flat File Contains Multiple Record Layouts

Sep 18, 2006

We have a flat file format generated from a vendor. It contains a "mainframe" view of the data with a header record, batch header record, detailed records, batch trailer record and trailer record. It arrives as a .dat file. What is the best approach to extract the necessary columns out of this file to populate the corresponding SQL server tables and rows?

View 3 Replies View Related

1 Flat File To 2 Destination Tables With Different Mappings :Please Help

Feb 21, 2007

Here is the Scenario.

I have a flat file with name "Employee.txt " (Full url: C:Employee.txt) .The File content is like this

Anil,Engineering,1997
Sunil,Sales,1981
Kumar,Inventory,1991
Rajesh,Engineering,1992

(Note: Items are Comma Seperated)

Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2".
The Table is like this.

TblEmp1 : Columns
EmpName EmpDept EmpjoinDate

TblEmp2 : Columns
EName EDate Edept

using integration services (SSIS) i need code(vb.net or c#) to Create a dtsx package so that i can push the flat file content to these 2 tables.
And the condition is :

After Executing the package Data loaded in TblEmp1 should be like this

EmpName EmpDept EmpjoinDate
Anil Engineering 1997
Sunil Sales 1981
Kumar Inventory 1991
Rajesh Engineering 1992

(No change in order compared to source)
And Data loaded inTBLEmp2 should be like this

EName EDate Edept
Anil 1997 Engineering
Sunil 1981 Sales
Kumar 1991 Inventory
Rajesh 1992 Engineering

Now, i know that we need to do like this in wizard
1) Create a flat file source component.
2) Create flat file connection and set the properties of flat file (delimeters and other things)
3) Create a Multicast Component.
4) Create a Path between Flat file source and Multicast.
5) Create 2 destination component(each for a table).
6) Create path from multicast to 2 destination components
7) Create a OledbConnection and set table names for 2 destination components..
7) Now,i have to do mapping for destination1.8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.

I have done it in wizard.I need to do it through code and i know that its not complicated.Please find the attached file with this mail.i have attached a screen shot of how i have done in wizard.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!

View 1 Replies View Related

Creating Hierarchical Flat File From Multiple Record Types

May 1, 2008

I'm using SSIS to import seven flat files (each containing a different record type) into a staging database. This part was easy.

Now I need to export the records from all seven tables into a single flat file structured in a nested hierarchy using common keys. (This format is required by the vendor for loading data into a new system).

I could use some ideas on the data transformations needed to combine all seven record types into an hierarchical record set which can then be written to my Flat File Destination. I'm currently looking at an article on SLQIS.com ("Handling Different Row Types In The Same File") which seems close to what I need, but they are importing (ref: www.sqlis.com/54.aspx ). I'm not sure if I should just reverse this for export or use something different. Any comments are appreciated.

Diagram of Record Hierarchy

typeA (parent key, ...)

typeB1 (parent key, childSet key, date, ...)

typeB2 (parent key, childSet key, ...)

typeC (parent key, childSet key, ...)
typeD (parent key, childSet key, ...)
typeE1 (parent key, childSet key, date, ...)

typeE2 (parent key, childSet key, ...)


The record types B1 through E2 form a complete set. Each set has it's own unique child-set key. There may be one or more sets for each typeA record (although it's possible that typeE records don't exist in the most recent set).

View 3 Replies View Related

How To Use Multiple Delimiters For The Same Flat File Source While Creating The Package

Sep 6, 2007


Hi everyone,


There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

View 9 Replies View Related

Unable To Edit Pre-defined Flat File Connection Manager Properties In The Flat File Destination Editor

Aug 24, 2007

Hi,

I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.

Did someone else faced this issue?


Thanks,
AQ

View 1 Replies View Related

Create Package For Multiple Table Insert From Flat File Source

Feb 23, 2008



Hello Guys,

I am working in one company and currently I am assigned to new project for Data Migration from company X to our company Y using SSIS. I am totally new and i just completed 5 tutorial which was gien on MSDN website.

Basically client is going to send us first flat file with 1 million records with Header, Detail and Trailer records.
I want to create a Package in such a way that it dumps all this first load into 7 to 8 different tables at a time.
we also have to include functionlity for validation and error check.
On successfull load error file should only return Header and Trailer but no detail records.
If there are any errors then error file should contain Header, Detail records which were unable to load plus trailer which we have to sent back to client.

When 2nd file comes that time we have to check whether this is new records or change (update) one depending on Flag which tells it.


This is basically high level idea of my Package what i need to create. If u guys have any question then let me know.

I know you guys are very experienced one. Anyone of you please give me some detail idea on it I would really appricate it.
I have very limited time line for it.

Thanks

Shah

View 4 Replies View Related

Flat File Connection Manager Throws Error When A Column Gets Added To The Flat File

Dec 27, 2006

Hi,

I have a situation where a tab limited text file is used to populate a sql server table.

The tab limited text file comes from a third party vendor. There are fixed number of columns we need to export to the sql server table. However the third party may add colums in the text file. Whenenver the text file has an added column (which we dont need to import) the build fails since the flat file connection manager does not create the metadata for it again. The problem goes away  where I press the button "Reset Columns" since it builds the metadata then. Since we need to build the tables everyday we cannot automate it using SSIS because the metadata does not change automatically. Is there a way out in SSIS?

View 5 Replies View Related

Output Column Width Not Refected In The Flat File That Is Created Using A Flat File Destination?

May 11, 2006

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

View 3 Replies View Related

Insert Data From Flat File Into Serveral DB Tables Having Many-to-many Relations

Nov 4, 2007

Hi everyone,

I am new to SSIS and I thought maybe someone would give me tips for solving the problem I am facing.


Overview:
I want to insert data contained in a flat file into several DB tables, which have N-M relations.

For illustration, I would explain the problem on a very simple DB:
1. The database contains the following 3 tables:
EMPLOYEE (EMP_ID, EMP_NAME)
PROJECT (PROJ_ID, PROJ_NAME)
EMP_PROJ (EMP_ID, PROJ_ID) , where EMP_ID and PROJ_ID are foreign keys referencing records in the EMPLOYEE and PROJECT tables respectively.


2. Each entry in the falt file contains the following data:
EMP_ID, EMP_NAME, PROJ_ID, PROJ_NAME


3. In SSIS, I have created a Data Flow Task containing:
- a path from a Falt File Source to an SQL Server Destination (Table: Employee)
- a path from a Falt File Source to an SQL Server Destination (Table: Project)
- a path from a Falt File Source to an SQL Server Destination (Table: Emp_proj)

Note: I used SQL Server Destination, because I need to import a huge amount of data and I read that this component performs better than the OLE DB Destination!


Questions:
1. I would like to eliminate EMP_ID and PROJ_ID from the Flat File Source. Instead, I would like these fields to be generated automatically upon insertion.
a. How can I do this and propagate the generated key among the different paths, which I have explained previously?
b. Can I first generate the two keys somehow then the parallel insertions into the different tables should start using the generated keys?


2. Is my solution correct in the first place? Or is there another better way for inserting data which belong to N-N relations?


Thanks in adavance,
Samar


View 5 Replies View Related

How To Redirect The Error Of A Source Flat File To The Destination Flat File?

Nov 10, 2006

Hi all,

I m using SSIS and i am transfering the data from Flat File Source to the OLE DB destination File. The source file contain some corrupt data which i am transfering to the other Flat file destination file.

Debugging is succesful but i am not getting any error output in the Flat file destination file.

i had done exactly which is written in the msdn tutorial of SSIS.

Plz tell me why i am not getting the error output in the destination flat file?

thanx

View 1 Replies View Related

Converting Flat File To SQL2005 Table (Flat File From H***)

Feb 11, 2008

First, a couple of important bits of information. Until last week, I had never touched SISS, and therefore, I know very little about it. I just never had the need to use it...until now. I was able to convert my first 3 flat files to SQL2005 tables by right clicking on "SISS Package" and choosing "SISS Import and Export Wizard". That is the extent of my knowledge! So please, please, please be patient with me and be as descriptive as possible.

I thought I could attach some sample files to this post, but it doesn't look like I can. I'll just paste the information below in two separate code boxes. The first code box is the flat file specifications and the second one is a sample single line flat file similar to what I'm dealing with (the real flat file is over 2 gigs).

My questions are below the sample files.


Code Snippet
Record Length 400

Positions Length FieldName

Record Type 01
1,2 L=2 Record Type (Always "01")
3,12 L=10 Site Name
13,19 L=7 Account Number
20,29 L=10 Sub Account
30,35 L=6 Balance
36,37 L=1 Active
37,41 L=5 Filler
Record Type 02
1,2 L=2 Record Type (Always "02")
3,4 L=2 State
5,30 L=26 Address
31,41 L=11 Filler
Record Type 03
1,2 L=2 Record Type (Always "03")
3,6 L=4 Coder
7,20 L=14 Locator ID
21,22 L=2 Age
23,41 L=19 Filler
Record Type 04
1,2 L=2 Record Type (Always "04")
3,9 L=7 Process
10,19 L=10 Client
20,26 L=6 DOB
26,41 L=16 Filler
Record Type 05
1,2 L=2 Record Type (Always "05")
3,16 L=14 Guarantor
17,22 L=6 Guar Account
23,23 L=1 Active Guar
**There can be multiple 05 records, one for each Guarantor on the account**


and the single line flat file...



Code Snippet
01Site1 12345 0000098765 Y 02NY1155 12th Street 03ELL 0522071678 29 04TestingSmith,Paul071678 05Smith, Jane 445978N 05Smith, Julie 445989N 05Smith, Jenny 445915N 01Site2 12346 0000098766 N 02MN615 Woodland Ct 04InfoJones,Chris 012001 01Site3 12347 0000098767 Y 02IN89 Jade Street 03OWB 6429051282 25 04Screen New,Katie 879500





As you can see, each entry could have any number of records and multiples of some of the record types, with one exception, every entry must have a "01" record and can only have one "01" record. Oh, and each record has a length of 400.

I need to get this information into a SQL 2005 database so I can create a front end for accessing the data. Originally, I wanted one line for each account and have null values listed for entries that don't have a specific record. Now that I've looked at the data again, that doesn't look like a good idea. I think a better way to do it would be to create 5 different tables, one for each record type. However, records 2 through 5 don't have anything I can make a primary key. So here are my questions...


Is it possible to make 5 tables from this one file, one table for each of the record types?

If so, can I copy the Account number in record 01, position 13-19 in each of the subsequent record types (that way I could link the tables as needed)?

Can this be done using the SISS Import and Export Wizard to create the package? If not, I'm going to need some very basic step by step instructions on how to create the package.

Is SISS the best way to do this conversion or is there another program that would be better to use?
I know this is a huge question and I appreciate the help of anyone who boldly decides to help me! Thank you in advance and I welcome anyone's suggestions!

View 13 Replies View Related

Converting A File Into Multiple Tables

Nov 15, 2006

This file format has multiple levels (X12).One level could have one or more instances of thenext level contained within it. Kind of like XML,except that some sections have no end tags, and theones that do have end tags actually have a _different_tag for the end. (ISA ...IEA or GS ... GE)It's easy enough to read a line at a time, see whattype it is, and insert its parts into the appropriatetable. Keeping track of the keys of the parent levelfor relationships.But I'm wandering whether there's some (not impossiblycomplex) more efficient method with SQL and/or DTS.--Wes GroleauIf you put garbage in a computer nothing comes out but garbage.But this garbage, having passed through a very expensive machine,is somehow ennobled and none dare criticize it.

View 8 Replies View Related

Exporting Multiple Tables To A Single File

Oct 1, 2004

I need to export data from multiple tables into one single file. The big problem here is that the tables will have different column types.

I am attempting to create something that allows users to be able to send me the contents of their tables's, through either email or ftp. I would prefer to make it easier for them so they only have to deal with one file, instead of the multiple files that bcp and dts create when exporting from multiple tables.

I was thinking of using DTS or BCP and then join (append) the files (either zip them or append the files together in some fashion), but I was hoping that there was an easier method out there.

Any ideas on how I may accomplish this would be greatly appreciated.

Andy

View 9 Replies View Related

Writing Multiple SQL Tables To One Text File

Apr 7, 2008

I am trying to create a text file from multiple SQL Tables using @BCP_Command. I tried using DTS and SQL but the number of columns in the tables have to be the same size when doing a union. I also not to place a delimeter between each column. I've learned how to use BCP_commands on one file not sure if you can make it work with two or more.

Rich Pezick

View 3 Replies View Related

Import Data From Text File To Multiple Tables

Feb 8, 2008



I have a text file which contains the data that has to be inserted into multiple tables.The columnames of table 1 form the H1 follwed by Details D1,D1,D1...
The column names of table two form the H2 followed by details D2,D2,D2 so on and similarly for Table 3.
Am using a link server to the file directory and schema.ini which defines the column names fofr the text file

Is there any way of defining column names for more than one table through the schema.ini? or is there any other way through I can parse the text file contents to multiple tables?


Sample text file:
H1,JobDate,JobNumber,FileName,
D1,13/02/2008,asdf123,text1.txt
D1,13/02/2008,asdf123,text2.txt
D1,13/02/2008,asdf123,text3.txt

H2,PagesUsed,PagesPrinted,Pages emailed
D2,10,10,9
D2,1000,100,99
D2,50,22,93


Schema.Ini - defined for the first table

[LogConfig.txt]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeaders=true
Col1=JobDateText Width 20
Col2=JobNumberText Width 20
Col3=FileName Text Width 100

Hoe do i define the column names for the second table. All these contents are in a single text file and need to be parsed only thru sql.

Any help/suggestions are welcome..
Thanks a lot for taking time to read this.



View 1 Replies View Related

How Can I Take This Example Flat File And Parse Out Each Section To A New Flat File? Each Section Starts With HD (header Row)

Mar 13, 2006

How can I take this example Flat file and parse out each section to a new flat file?  Each section starts with HD (header row)

http://www.webfound.net/flat_file_example.txt

e.g. an example output file based on above (cutting out the first section) would be:

http://www.webfound.net/flatfile_output.txt

Also, I'll need to grab a certain value in each header row (certain position in the 100 byte header row) to use that as part of the filename that's outputed.  I assume it would be better to insert these rows into a temp table then somehow do a search on a specific position in the row...but that's impossible?  The other route is to insert each row into a temp table separated out by fields but that is going to be too combursome because we have several formats to determine separation of fields based on the row type so I'd have to create many temp tables and many components in SSIS when all we want to do is again:

1) output each group (broken by each header row) into it's own txt file

2) use a field in  the  header row as part of the name of the output txt file (e.g. look at the first row, whcih is a header row in flat_file_example. txt.  I want to grab the text 'AR10' and use that as part of the filename that I create

Any suggestions on how to approach this whole process in SSIS...the simplest approach that will work ?

View 1 Replies View Related

Integration Services :: How To Load Multiple Tables Data Into Single Excel File

Aug 26, 2015

My Requirement ,In Source Database 5 tables are there ( Emp,Loc,dept,Time,Product ), Destination is Single Excel file.But Dynamically how to load each table information to load into each sheet wise through SSIS Package?

View 3 Replies View Related

Integration Services :: SSIS Split Single Input File Data Over Multiple Tables?

Sep 30, 2015

I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes.  The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length. 

Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).

The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.

View 8 Replies View Related

Integration Services :: Loading Multiple XML File With Different Metadata In Server Tables With SSIS 2008

Feb 17, 2011

I have multiple xml data file in a directory say C:XMLData abc1.xml, abc2.xml, abc3.xml etc.

Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB.

Next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table.

While each iteration, xml source should also point each xsd file correspondingly.

 Tables are already created in DB

I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation.

Just wanted to know how can I read each xsd file for each xml data files while iteration. 

View 12 Replies View Related

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables

Mar 21, 2008

Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:


1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.

1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.

1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.

I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.

Thanks in advance for any help/suggestions,
Bob

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved