ADD column storeID while bulk inserting

Question

Post reply

ADD column storeID while bulk inserting

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

daniarchitect SSCommitted Points: 1727 More actions · Answer 1

daniarchitect

SSCommitted

Points: 1727

May 31, 2010 at 6:44 am

#1174484

thanks guys

daniarchitect SSCommitted Points: 1727 More actions · Answer 2

daniarchitect

SSCommitted

Points: 1727

May 31, 2010 at 6:47 am

#1174486

someone suggested to use batch programming on the files to insert storeid.

Jeff thank you

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (5/30/2010)

Matt Miller (#4) (5/30/2010)

I no longer have a functional version of DTS (my 2K server is history). That said (from memory, so bear with me):

- First would be an ActiveX task, to check the designated directory and pull all files names. Dump the files into a table

- upon completion of step 1, fire a data-driven task, set up to fire a BulkInsert task for each row in the table populated by step one (clearly you'd need a completion status, etc...).

- if you're inserting into a staging table - once everything has inserted - scrub the data appropriately, then push to the permanent table. That would be a "regular" sql Script task, then a SQL destination.

Well done, especially from memory. You'd also need some loop code to decide if you where done or not and the actual "go back to the beginning" loop code. It all takes "Active X" (VBS) just about every step of the way. A "decent" example of how to do this in DTS can be found at http://www.sqldts.com/246.aspx. I put "decent" in quotes because it takes a lot of code to make those little icons actually work properly.

I think it's long, ugly, requires more than 1 language, and totally unnecessary. The tricks you have to go though in DTS (or even SSIS) to do something like a simple loop have always amazed me as to how easy they could have been instead of having to write a module to enable/disable legs. Even though SSIS is an improvement, I feel much the same way about it... long, ugly, requires more than 1 language, and totally unnecessary. Heh... Even a looping DOS batch file would be simpler. 😛

I guess I'm going to have to pick back up on VB.NET or C#, write a decent and permanent replacement for xp_DirTree, add a tight little "archive move" module, and make it so that it's so easy to do in T-SQL (already really easy) that no one will even want to import flat files or XML files using DTS or SSIS ever again.

Now, if we could just get MS to write a decent replacement for the JET drivers, we'd be all set. 🙂

True on just about all points (some are opinions so I certainly am not going to fight them). SSIS is definitely an "acquired taste", but it's one of those "stare at it a while and then it all clicks" or at least was to me.

Looping was a bit painful in DTS, and yes - if I need to fully circle back and verify, then I would need yet another ActiveX. That said - that wouldn't be the case in SSIS, since you have built-in constructs for looping, one of the default settings being...for all files in a dir matching x pattern...

Finally - If you'd care to drop some specs as to xp_dirtree you'd like to see....it might just materialize....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

daniarchitect (5/31/2010)

someone suggested to use batch programming on the files to insert storeid.

Jeff thank you

Did that someone give you the batch code to do it? And did they show you how to do it without the use of logins and passwords?

Also, do you need to move files that have been processed to an archive directory or are you just marking the files as "processed" somehow?

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Matt Miller (#4) (5/31/2010)[hrTrue on just about all points (some are opinions so I certainly am not going to fight them). SSIS is definitely an "acquired taste", but it's one of those "stare at it a while and then it all clicks" or at least was to me.

I can see that. And, yeah... I was being a bit opinionated. Thanks for recognizing that.

Finally - If you'd care to drop some specs as to xp_dirtree you'd like to see....it might just materialize....

Very, very cool and very generous of you. I'll put together a small package that will explain all. Thanks, Matt.

--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)
Intro to Tally Tables and Functions

daniarchitect SSCommitted Points: 1727 More actions · Answer 6

Hey Jeff and all

He didnt actually gave me the batch code for that,

but I found out the following which is close to my case(our case at this point:-))

http://www.computing.net/answers/programming/appending-the-timestamp-as-a-column/16587.html

please you may refine and make this to work for us,am not comfortable at Batch programming

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

I guess I wouldn't do it that way then. Have you tried the code I posted?

--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)
Intro to Tally Tables and Functions

David Burrows SSC Guru Points: 65111 More actions · Answer 8

Jeff Moden (5/31/2010)

I guess I wouldn't do it that way then. Have you tried the code I posted?

Your code is simlar to what I have done in the past, either using xp_cmdshell or from DOS bat file (using FOR %%f IN), never had problem with either.

And btw Jeff, did you know, admin priviledge is required on tempdb database to enable bulk insert to temp tables which is why I use permanent staging table.

Far away is close at hand in the images of elsewhere.
Anon.

daniarchitect SSCommitted Points: 1727 More actions · Answer 9

daniarchitect

SSCommitted

Points: 1727

June 1, 2010 at 3:21 pm

#1175028

Hey Jeff,

I have been trying it the whole night yesterday and all the day today.. and it didn't work out.. (it goes well till some point and then generates error..).. and couldn't figure out

Am really tensioned...????

Thanks a lot

LutzM SSC Guru Points: 107049 More actions · Answer 10

daniarchitect (6/1/2010)

Hey Jeff,

I have been trying it the whole night yesterday and all the day today.. and it didn't work out.. (it goes well till some point and then generates error..).. and couldn't figure out

Am really tensioned...????

Thanks a lot

I assume Jeff will be asking for the error message you mentioned as well as a more precise description of "some point", meaning some sample data that will actually show the error. Since Jeff isn't online at the moment you might want to use that time to get the info together. It sure will help. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

lmu92 (6/1/2010)

daniarchitect (6/1/2010)

Hey Jeff,

I have been trying it the whole night yesterday and all the day today.. and it didn't work out.. (it goes well till some point and then generates error..).. and couldn't figure out

Am really tensioned...????

Thanks a lot

I assume Jeff will be asking for the error message you mentioned as well as a more precise description of "some point", meaning some sample data that will actually show the error. Since Jeff isn't online at the moment you might want to use that time to get the info together. It sure will help. 😉

Zactly. 😉

{edit} And, to be sure, my question was never answered... did you try my code or ??? Like Lutz suggests, I need the actual code you used, the data (as an attachment in this case) that caused the error, and an exact copy of the error message that came up.

--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)
Intro to Tally Tables and Functions

daniarchitect SSCommitted Points: 1727 More actions · Answer 12

Error I get is the following

(2 row(s) affected)

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (OrderDate).

(0 row(s) affected)

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (OrderDate).

(0 row(s) affected)

it perfectly creates and populates the dirlist and filelist tables but it cant populate the staging and dest tables.

in case you need the defnition for BulkInsert Table:

USE

GO

/****** Object: Table [dbo].[OrdersBulk] Script Date: 06/01/2010 22:46:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OrdersBulk](

[CustomerID] [int] NULL,

[CustomerName] [varchar](32) NULL,

[OrderID] [int] NULL,

[OrderDate] [smalldatetime] NULL,

[storeID] [varchar](5) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

and two of sample data's created in C:\Temp

saleData0123.txt

1,foo,5,20031101

3,blat,7,20031101

5,foobar,23,20031104

saleData0484.txt

15,foo,5,20031105

30,blat,7,20031104

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Ummm... ok.... where's the code that does the actual work of importing?

--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)
Intro to Tally Tables and Functions

daniarchitect SSCommitted Points: 1727 More actions · Answer 14

daniarchitect

SSCommitted

Points: 1727

June 2, 2010 at 3:25 am

#1175157

Jeff,

FInally it works!!!!!!!!!!!!

Thanks so much,have removed the character encoding,and changed the rowdelimiter........it actually imported 98 of 144 files and I think the rest requires some data clean up.

Be blessed!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

daniarchitect (6/2/2010)

Jeff,

FInally it works!!!!!!!!!!!!

Thanks so much,have removed the character encoding,and changed the rowdelimiter........it actually imported 98 of 144 files and I think the rest requires some data clean up.

Be blessed!

Very Cool! What did you change the rowdelimiter to?

Also, have you been able to determine what's wrong with the data in the other files? If they turn out to be "spurious pips" in occasional rows, there is a way to import most of the data without error and sequester the bad rows for human review. I just need an example of the bad data.

--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)
Intro to Tally Tables and Functions