Split file and load into Target table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Split file and load into Target table

Author

Topic

krishtxraj19
Starting Member

8 Posts

Posted - 2009-02-26 : 21:19:47

HI all, i am new to SQL Server Integration Services. I am trying to load list of flat files into target table. I am using Foreach loo to do that.
But my files data is different than regular files.

The first row of the file is file name and row count and from second row, it has the actual data. here is the example

Cust_America 1000
10011 Chris Brown TX US
10012 Chalrs Brown FL US
10013 Martin James IL US

now i want to load first row in different table called daily audits and from second row i want to load in my customer dimension.

Can someone help me on this scenarion.

Thanks in Advance

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 09:44:09

Who is the provider of that file? Can you ask the provider of that file to add a bogus colum for the first row such as date or somethin like that. Otherwise dump the result of file into intermediary or staging table then from staging table first row to table A and rest of rows to table B
if you cannot change the structure of the file the staging table approach should be good.
StagingTable

Field1		Field2		Field3	Field4
Cust_America    1000		NULL	NULL
10011		Chris Brown	TX	US
10011		John Brown	TX	US
10011		Larry Brown	TX	US

krishtxraj19
Starting Member

8 Posts

Posted - 2009-02-27 : 10:26:32

Hi, thanks for the reply. The author of the file is no one. I have created a file for my practice. I am trying to to load the data without data field. And my idea is to dump the data in staging area ad from there to DW.

But i am using Conditional split transformation and Script component to seperate first record to load in one table(Called Audit) and the other data into Customer_Stg table. Problem with this is i am able to load Audit table but not Customer_Stg table. Data is passing successfully from conditional split, but from there my data flow task is failing.

Don't know what is the best way to do this.

Suggessions are gratly aprreciated.

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 10:36:46

1. what is the error you are getting
2.i would do this then make your staging as follows

Field1		Field2		Field3	Field4
Cust_America	1000		NULL	NULL
10011		Chris Brown	TX	US
10011		John Brown	TX	US
10011		Larry Brown	TX	US

then eeither say first row is to table a or conditionally say where field3 field 4 is NULL or you can make field3 and field4 default value to -1 instead of NULL then you can say WHERE Field3 = -1 put that to table a..something like that?

2 cents

krishtxraj19
Starting Member

8 Posts

Posted - 2009-02-27 : 11:01:28

[OLE DB Destination 1 [592]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

This is the error message i am getting when i run the package.

I can see data passes through all transformations except loading into Target.

The idea which you gave is good, can we do that without creating temp table?? Like load audit data in audit table and Cutomer data i Customer_Stg table.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 11:29:40

why not make the staging table generic since that is the whole purpose of a staging table, instead of making it Customer specific, you can make it Staging. then populate it with both customer and audit then seperate your data from there. or you can have two staging tables one for customer one for audit.

krishtxraj19
Starting Member

8 Posts

Posted - 2009-02-27 : 11:56:59

I am trying to follow your step 2. I.e,

i would do this then make your staging as follows

Field1 Field2 Field3 Field4
Cust_America 1000 NULL NULL
10011 Chris Brown TX US
10011 John Brown TX US
10011 Larry Brown TX US

I am doing this Using script component. But still i am getting the same error.

Error: 0xC0209029 at Data Flow Task, Data Conversion [844]: The "output column "Copy of Col2" (860)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Col2" (860)" specifies failure on error. An error occurred on the specified object of the specified component.

Can you suggest any better way other than script component to achive above scenario

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 13:26:26

I would read up on
BULK INSERT
OPENROWSET
etc also a FIELDTERMINATOR would be nice in that file will be helpful.
can you make the file delimited tab or otherwise and the first row have more than just Cust_America 1000. it makes it easier if it is Cust_America,1000,'','' or Cust_America{tab}1000{tab}''{tab}''

2 cents

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 13:33:24

Create file called CommaDelimited.txt with data as follows

Cust_America,1000,,
111,Bling Bling,TX,TX
10011,Chris Brown,TX,US
10011,John Brown,TX,US
10011,Larry Brown,TX,US

CREATE TABLE Staging(field1 varchar(max) NULL,
field2 varchar(max) NULL,
field3 varchar(max) NULL DEFAULT ((-1)),
field4 varchar(max) NULL DEFAULT ((-1)))
GO
BULK INSERT Staging
FROM N'\\ServerName\Folder\BULK INSERT TEST\CommaDelimited.txt'
WITH
(
FIELDTERMINATOR =','
,CODEPAGE = 'RAW'
)

SELECT * FROM Staging

krishtxraj19
Starting Member

8 Posts

Posted - 2009-02-27 : 15:54:49

I am trying to load 5 files starts with Cutomer and all the files has same structure like first row is file name, count of records and from second row the actual data and it is Tab delimited file. So i am using foreach loop container. But when i try to use foreach loop container with bul insert task, i can not set the connection manager there?

Can you Suggest anyother way other than Bul Insert task.

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-27 : 17:56:18

yes you can set the connection manager you have to create it on bottom tab where it says connection managers . right lick and select new ole db and select or create (click New...) to point to server/database where your staging tabel exists in.
then in your FOR EACh loop container you add a SQL task. in the SQL task property in the [SQL Statement ] property add the BULK INSERT command string and make sure Connection peroperty points to the new OLE DB you created earlier (which should show in the bottom tab Connections manager)
I tested it and it works. I added abotu 15 files and it populates the data

krishtxraj19
Starting Member

8 Posts

Posted - 2009-03-02 : 11:43:17

Hi good morning! I got an another idea regarding this.

Can we use same flat file source multiple times for Header record and for detail records? And i want to use this in two different dataflows connected each other. One data flow loads only one record i.e. Header record(with a conditional split) and another data flow loads detailed records(Selected in source file connection properties as skip first row).

Can i folllow the above method using Foreach loop container??

Please let me know if this is not clear

Thanks
Krish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources