How do I Import textfile into SQL table using schema

I've been importing delimited textfile data into Access db using a Schema.in
i
file
to describe the text like this
[Import.TXT] ( where the raw text is )
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1 = Field1 text width 255
Col2 = Field2 text width 255
Col3 = Field3 text width 255
Col4 = Field4 text width 255
With the following code ( VB )
'{Microsoft Text Driver (*.txt; *.csv)}
' paths for the jet engine to find schema & files + text driver for
importation
strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
"DBQ=" & filepath & ";" & _
"DefaultDir=" & filepath & ";" & _
"Uid=Admin;Pwd=;"
' use ADO - init connection
Set adoCn = New ADODB.Connection
adoCn.Open strCn
then
strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
strSQL = strSQL & "FROM " & txtfile
adoCn.Execute strSQL
to import the text into the appropriate fields in the table.
How do I do this with SQL Server 200?
thanks -- jackTry using DTS Wizard to do the job !
run at shell command "dtswiz"
Pollus Brodeur|||Hi
Along with DTS, you can use BCP or the BCP API. You may also want to look at
the BULK INSERT statement.
John
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||Jack,
If the import file will always be in the same directory, you could
set up a linked server to that directory. This will automatically
use the specifications in the schema.ini file. Here is an example
for exporting, but it should be simple to adapt for importing.
1) Create a directory for the text files. I chose E:\txtsrv
2) Add this as a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'e:\txtsrv',
NULL,
'Text'
3) Put an empty text file in this directory (I used empty.txt).
You can do this with master..xp_cmdshell, but I didn't. You
can't do CREATE TABLE on the linked server.
4) Create a schema.ini file to describe how this file should
represent your table. One schema.ini file is used for all the
tables of the linked server.
This is my schema.ini:
[empty.txt]
Format=FixedLength
ColNameHeader=True
Col1=CustomerNumber Long Width 11
Col2=CustomerName Text Width 30
Col3=CustomerDate DateTime Width 25
5) Populate the text-table, replacing the filename's . with # for the
table name.
insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
CustomerDate)
select orderid, customerid, orderdate
from northwind..orders
order by customerid
Steve Kass
Drew University
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
> I've been importing delimited textfile data into Access db using a
> Schema.ini
> file
> to describe the text like this
> [Import.TXT] ( where the raw text is )
> ColNameHeader=False
> Format=CSVDelimited
> CharacterSet=ANSI
> Col1 = Field1 text width 255
> Col2 = Field2 text width 255
> Col3 = Field3 text width 255
> Col4 = Field4 text width 255
> With the following code ( VB )
> '{Microsoft Text Driver (*.txt; *.csv)}
> ' paths for the jet engine to find schema & files + text driver for
> importation
> strCn = "Driver=" & "{Microsoft Text Driver (*.txt; *.csv)}" & ";" & _
> "DBQ=" & filepath & ";" & _
> "DefaultDir=" & filepath & ";" & _
> "Uid=Admin;Pwd=;"
> ' use ADO - init connection
> Set adoCn = New ADODB.Connection
> adoCn.Open strCn
> then
> strSQL = "SELECT * INTO [" & tbl & "] IN '" & dbName & "'"
> strSQL = strSQL & "FROM " & txtfile
> adoCn.Execute strSQL
> to import the text into the appropriate fields in the table.
> How do I do this with SQL Server 200?
> thanks -- jack|||PollusB,
I was able to use the DTS Wizard to get a CSV file into my Table, but I
need to do this programatically on many files every day so a manual operatio
n
is not practical. I have seen some information that indicates DTS can be
used as an object etc., but I have no clue at this point about how that migh
t
be done. If you know how the task can be done using DTS internal to a VB ap
p
I'd sure appreciate it.
thanks for your help -- jack
"PollusB" wrote:

> Try using DTS Wizard to do the job !
> run at shell command "dtswiz"
> Pollus Brodeur
>|||John,
I tried to use a BCP format file, but since my input file has varying
number of columns per row, I wasn't able to get it to work. I've tried the
Bulk Insert approach
and it has the same problem. It wants to have the input 'line' contain the
exact
number of fields as the table, otherwise it gives an unexpected end of line
errror.
I've pretty much given up on the Bulk Insert method because I couldn't find
a method to get around it's limitations. I'm currently looking at how to us
e
DTS in
my application to get the files imported.
Thanks for your suggestions -- jack
"John Bell" wrote:

> Hi
> Along with DTS, you can use BCP or the BCP API. You may also want to look
at
> the BULK INSERT statement.
> John
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>|||Steve,
The technique you've suggested is almost verbatim the method I used for
Access
databases. It worked very well in that environment. I couldn't get the
'linked server' to work; I'm actually not familiar with that technique.
Thanks for your suggested solution and code -- jack
"Steve Kass" wrote:

> Jack,
> If the import file will always be in the same directory, you could
> set up a linked server to that directory. This will automatically
> use the specifications in the schema.ini file. Here is an example
> for exporting, but it should be simple to adapt for importing.
> 1) Create a directory for the text files. I chose E:\txtsrv
> 2) Add this as a linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'e:\txtsrv',
> NULL,
> 'Text'
> 3) Put an empty text file in this directory (I used empty.txt).
> You can do this with master..xp_cmdshell, but I didn't. You
> can't do CREATE TABLE on the linked server.
> 4) Create a schema.ini file to describe how this file should
> represent your table. One schema.ini file is used for all the
> tables of the linked server.
> This is my schema.ini:
> [empty.txt]
> Format=FixedLength
> ColNameHeader=True
> Col1=CustomerNumber Long Width 11
> Col2=CustomerName Text Width 30
> Col3=CustomerDate DateTime Width 25
> 5) Populate the text-table, replacing the filename's . with # for the
> table name.
> insert into txtsrv...empty#txt(CustomerNumber, CustomerName,
> CustomerDate)
> select orderid, customerid, orderdate
> from northwind..orders
> order by customerid
> Steve Kass
> Drew University
> "hushtech" <hushtech@.discussions.microsoft.com> wrote in message
> news:A78B5213-B579-44CC-AA3F-7949BC661C7E@.microsoft.com...
>
>

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

How do I import Excel file into SQL Express??

Hello,

I am total beginner to SQL and want to import an Excel file into SQL but I'm not sure where to start. The Excel file is a small company directory, names, addresses etc., not too big.

Any help would be greatly appreciated!!!!hi..
my adivice is dont use excel, create a MS-Access data base and import that into sql..|||thanks for the post.

Assuming I am able to create an MS Access database, what are the steps to importing this file into SQL?

thanks|||1.right click on any database
2.select TASKS and then select IMPORT DATA after clicking
this one dialog box will appear,click NEXT
3.Choose MICROSOFT ACCESS as DATA SOURCE,then BROWSE ur access file then click NEXT
4.Chosse the DESTINATION[SQL native client]
5.Select the SERVERNAME
6.There are two AUTHENTICATION, select the SQL SERVER AUTHENTICATION and give the USERNAME & PASSWORE
7.Select the DATABASE and then click next
8.select "COPY DATA FROM ONE OR MORE TABLES OR VIEWS" then click next
9.the tables what you created in access will be showed here
10.select the tables which you want to insert and then click next
11.select the "EXECUTE IMMEDIATELY" check box and then click next
12.click finish
13.it takes some time to process and finallY, if there are no mistakes,it executes
successsfuly else it shows the error message|||You can't Import / Export using the Express editions.|||

Quote:

Originally Posted by Tros

You can't Import / Export using the Express editions.

Seems it may be round about, Excel -> Access -> SQL Express.|||When you download the SQL Express software, I believe it comes with a 'wizard' called DTS Wizard. It allows you to transfer among all the previously mentioned types.sql

How do I import a dts storage file into SQL?

Hi All
I have a DTS storage file (*.dts) with no network
connectivity to another SQL server (I have to mail the
storage file there).
How do I import the *.dts file into the msdb database so
that I can edit it from the DTS package designer?
Thanks
JonoSorry to bother all...found where to do it.
Thanks anyway
J

How do I import a db into MSDE2000?

How do I import a db into MSDE2000? Can it be done? I backed a db up from
another server and now I want to import on to this new machine. Not sure how
to go about doing that though...
Thanks.
=?Utf-8?B?TWFyaw==?= <Mark@.discussions.microsoft.com> wrote in
news:08A53C86-D697-4147-9277-B859B44F5D2C@.microsoft.com:

> How do I import a db into MSDE2000? Can it be done? I backed a db up
> from another server and now I want to import on to this new machine.
> Not sure how to go about doing that though...
> Thanks.
>
>
Use, e.g. in OSQL,
RESTORE HEADERONLY FROM DISK = 'backup-file-name-and-path'
to get a listing of the contents of the backup file to determine the
logical database name.
Then, if it is for a single mdf/ldf file pair,
RESTORE DATABASE [NewDatabaseName]
FROM DISK='backup-file-name-and-path'
WITH MOVE 'logical-database-name' TO 'NewDatabaseFilename',
MOVE 'logical-database-name_log' TO 'NewDatabaseLogFilename'
The NewDatabaseFilename should have a .mdf extension and the
NewDatabaseLogFilename a .ldf extension.
See RESTORE in BOL for further details.

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scott
http://support.microsoft.com/default...b;en-us;320125
David Portas
SQL Server MVP
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott
|||Thank you very much. It worked.
"David Portas" wrote:

> http://support.microsoft.com/default...b;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
>
>

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scotthttp://support.microsoft.com/defaul...kb;en-us;320125
David Portas
SQL Server MVP
--
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott|||Thank you very much. It worked.
"David Portas" wrote:

> http://support.microsoft.com/defaul...kb;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
>
>

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scotthttp://support.microsoft.com/default.aspx?scid=kb;en-us;320125
--
David Portas
SQL Server MVP
--
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott|||Thank you very much. It worked.
"David Portas" wrote:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> > Is there a way to copy all the diagrams from one database to a duplicate
> > database?
> > Thank you.
> >
> > Scott
>
>

How do I export a DTS from one server and import into another?

Good day all!

Hopefully this is just a quickie but we'll see, won't we? :p

I have a DTS package that needs moving from one server to our test bed, which is completely disconnected from the network - therefore I have to transport it by removable media (USB flash drive for instance).

Is there anyway I can do this? I've tried doing a "save as..." Structured Storage File (*.dts) but then I have no clue as to what to do with it to get it back in to the database!

Any suggestions me hearties?
Yarr!Yeah It is a quite easy if you are using same version of SQL on two boxes...
From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
Copy this file on the disconnected box and Import.

Hope this will help you.|||Dead easy this one. Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste ;)

I've never had much luck moving DTS packages around (portability is another nail in their coffin IMHO). Have you tried googling? You will find lots of articles about this. Also check out http://www.sqldts.com/ - there ain't much to do with DTS that ain't on there.|||From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
Copy this file on the disconnected box and Import.

It appears to be trying to export the tables as well? I think I might just be missing something

Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste

Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQL

Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.

Oh and another justification for DTS in this case - it needed to be done quick. It's for the purpose of the first bulk load of data from another source a number of times (test, test and test again!).

Any other ideas peoples?|||Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.Really? Article linked from the home page:
http://www.sqldts.com/204.aspx|||Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQLBulk insert - wrapper for BCP. Check out in BoL. Gets the stuff in:


BULK INSERT my_table FROM 'C:\stuff.csv'
WITH ( codepage = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 1, BATCHSIZE=1000, MAXERRORS=100000)


BCP for knocking it out:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM my_table" queryout "C:\stuff.csv" -c -T -t","'|||in sql 2K all you have to do is open up the package in the designer and do a save as structured storage file. Then on your other server all you have to do is open that file and save it to that sql server.

you will have to change the properties of your connection objects and anything else that points to things on the network. it's easy.|||The simplest method is to use Enterprise Manager to open an existing package, and then select Save As from the Package menu. You can then save the package directly to another server, or change the storage location to save it to a structured storage file if you do not have direct access to the final destination. The storage file can then be manually copied to the destination server. At the destination, open Enterprise Manager and right-click the Data Transformation Services node to access the Open Package option. This allows you to select a structured storage file package to open. You can then use the Save As option to save it to the new server.

I don't quite know how I missed that bit! :(

Poots, what does; "-c -T -t" mean/do?
I'm going to look into making it into a SP after we have the thing running anyhow. For now we needed the quick solution so that's what I provided.

Thanks guys!
I'll be back to bug you when I'm scripting this out soon ;)|||Poots, what does; "-c -T -t" mean/do?Would you like me to type BCP into the BoL search for you? ;)|||I'm getting lost in acronyms... BCP = Best Common Practice?
Nope, apparently it's Bulk Copy Program...

If I get time later I'll go look it up properly (gotta hit the test bed now if I still want to go on holiday next week ;))|||BCP is a very fast method of moving data about. Remember the "remove identity property from column" thread? One suggested solution was BCP the data out, change the table, BCP back in.

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?

How do I edit an Imported DTS package in SSIS?

I have used the wizard to import a DTS package from a SQL 2000 server to our new SQL 2005 server and need to make edits to reference the new server and database. I am able to see the package within the SQL Management studio under Integration Services, but I cannot find it in the Development studio?

What am I missing?

Thanks

The development studio is an offline tool that works on file packages only. Export the package from SQL Server / IS and add it to a project in BIDS.

How do i do this with dts

Im using DTS to a basic import of a products csv into a corresponding table in sql server. I use the transformations tab to map up all the fields between the two. I have one field in the sql server table that I need to include a value everytime the dts is run. So for eg one of the fields in the sql table is ProductTypeCode and in this case it should always be 1. Now as a cheap and dirty workaround I could add another column in my csv and give all the fields values of 1 and map that column to my ProductTypeCode in sql server table but surely there a more correct way of doing this simple task.

Thanks in advanceYou can create a query for the source, using the Excel table, you can add a 1 to that query. So when you set up the source, use a query, then use the query builder to setup the initial design from the excel file. Then modify the resulting query to add the 1 parameter.

Brian|||Try this url for all your DTS questions most problems are covered here, it is run by Darren Green a SQL Server MVP.
http://www.sqldts.com Hope this helps.

Kind regards,
Gift Peddie