Personally
1. Doing a text file in this way means you are processing it Row*Row. This
could get bogged down and hit you for performance. I use the staging table
approach
2. You can use the same connection each time sure but you can't use it as
the source and the destination so if you want to go from SQL Server to SQL
Server at any point you will need a minimum of 2.
3. If you use lookups then you need a seperate lookup connection also
otherwise bad things with elisted transactions start to happen
4 Dynamically setting all this stuff is a very good idea and I understand
your needs but how many connections are you going to need and will that
really be an admin headache
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
[quoted text, click to view] "Jase" <jase74@hotmail.com> wrote in message
news:399e053d.0407200705.637d0198@posting.google.com...
> Hi,
>
> Is there anyway to use the same SQL Server connection and create a
> transformation task between the two connection.
>
> What I'm trying to do is load data from a flat file into a staging
> table and transfer this data into the application tables, but I would
> like to use the activex scripting to do various checks on the data as
> it is transferred.
>
> Text File (Source) >> Transform Data into staging table >> Connection1
> >> Transform Data into application tables >> Connection1
>
> I have Dynamic properties object that will set the connection
> parameters i.e. username, password, database, and server but I would
> like this to set all connections and it would be easier to use the
> same connection.
>
> My real aim is to make the package easy to transfer from server to
> server, for purpose of support and testing the package, so I do not
> what to hard code any connection information into the package.
>
> Any help much appreciated.
