July 12, 2009 at 4:19 pm
I have a process that will be dumping comma delimited files - one line per file - into a folder. These will be uniquely named and I may have more than one file in the folder at a time to be processed. Need to use a wildcard - c:\import\*.txt, or something.
I have gone through the Import/Export wizard - and this works great - but I can't specify multiple files. I can process one file if I set the name to what is in the package.
Can I use the import/exp wizard with multiple file names, or read from a folder?
And, very important, I need to delete each file after the data is inserted or better, move to a processed folder - c:\import\processed.
Table looks like this -
CREATE TABLE [dbo].[xxorder](
[line_id] [int] IDENTITY(1,1) NOT NULL,
[pat_id] [int] NULL,
[ndc] [varchar](11) NULL,
[qty] [int] NULL,
[clinic] [varchar](30) NULL,
[date] [datetime] NULL,
[status] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[line_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Text file looks like this - 1026,23490644804,12,Johnny Thunders,
I map the first field in the file to the pat_id, second field to NDC, third filed to qty and fourth file to clinic.
Appreciate any assistance with this request - would be a huge time savings if someone could point me to a specific web resource, or detail it out.
Thanks!
July 12, 2009 at 5:55 pm
The import/export wizard is designed to ad hoc import a file, not process lots of files. When you run the import/export wizard, you can build an SSIS package as the result and the modify that. A FOR loop will let you go through a series of files.
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply