DTS import

Hi Folks,
I want to import the contents of several text files into a single sql server
table, however I also want to add in an additonal field which will give some
sort of indication which source file the row came from.
So for example.
File1.csv
a, b, c
File2.csv
x, y, z
sqlTable
a, b, c, 1
x, y, z, 2
Any ideas how can I could do this, I know I could import to tempory tables
and set the indicator field with a default value and then copy this data
into the final table but this seems like I would be adding in unnecessary
steps.
Cheers in advance
Charlie.From the example given it would seem that you need
A. A loop
B. A Global Variable that you can set.
Basics are that on the setup of the package you map Source --> Destination
columns but you also (In an Active X transform) map a Global Variable to a
destination column.
At each iteration you can then set the GV to whatever value you want
(Perhaps name of file is good).
Here is a looping example.
Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thanks Allan,
It looks a bit complex, but I'll give it a go, I like a challenge!
Charlie.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> From the example given it would seem that you need
> A. A loop
> B. A Global Variable that you can set.
>
> Basics are that on the setup of the package you map Source --> Destination
> columns but you also (In an Active X transform) map a Global Variable to a
> destination column.
> At each iteration you can then set the GV to whatever value you want
> (Perhaps name of file is good).
> Here is a looping example.
> Looping, Importing and Archiving
> (http://www.sqldts.com/Default.aspx?246)
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||I've had a look at this and downloaded the sample file from the website, but
its a bit beyond me, I don't mind setting up seperate DTS packages for each
file import if this makes things simpler.
Charlie.
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:RTF3b.222$b82.110168@.newsfep1-win.server.ntli.net...
> Thanks Allan,
> It looks a bit complex, but I'll give it a go, I like a challenge!
> Charlie.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> > From the example given it would seem that you need
> >
> > A. A loop
> > B. A Global Variable that you can set.
> >
> >
> > Basics are that on the setup of the package you map Source -->
Destination
> > columns but you also (In an Active X transform) map a Global Variable to
a
> > destination column.
> > At each iteration you can then set the GV to whatever value you want
> > (Perhaps name of file is good).
> >
> > Here is a looping example.
> >
> > Looping, Importing and Archiving
> > (http://www.sqldts.com/Default.aspx?246)
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
>|||OK.
Setting up another package for every file will be OK if you have 2 or 3
files but will quickly become unmanageable if you have 5000.
We can go through it if you wish.
Are you in the UK (email suggests this)
I am doing a presentation at MS Reading next week and this is one of the
things I am covering.
See here
http://www.sqlserverfaq.com/?eid=32
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org