How do I link tables to another database?

With Access, I could link to tables in a foreign db, even a csv file, and
query them as if they were local tables. How do I do that in SQL Server?Check out OPENROWSET and OPENQUERY in BOLsql

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

May I suggest posting this question on sql server programming news group?

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

Thanks

|||

You might get extra help in sql server programming news group for these kind of questions.

thanks

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

|||Thanks. I'll do that.

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file into an existing SQL databas

How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql

How do I get it to a precisions scale of .00?

I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

Try creating a derived column cast to DT_NUMERIC and scale of 2 between your data source and destination. That should allow you to get 2 decimal places for any numeric input type. You can add all the columns you want to do this to in the one derived column task.

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