Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
PatrickPatric
2 things. First make sure that the scheduled task is running either as SA or the Domain ID that the SQL Server is running under
2 make sure that the ID that SQL is running as has access to the directory in question
Jef
MCDBA, MCSE+I|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>
Showing posts with label csv. Show all posts
Showing posts with label csv. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
DTS Error
I need to export some table data to Excel which I normally do with Query Analyzer as a .CSV file. However, one of the fields has a ton of comma's which screws up the formatting so I'm using the DTS Import/Export Wizard. I choose the .xls file and paste my query. When it runs I get the following error: "Failed to copy 1 table(s). Double click the error..." When I click on the error it gives me an "Invalid Pointer" message. I'm not sure where to go from here so any help would be greatly appreciated.
Thanks,
Jim
I figured it out...I had "USE (Name of Database)" in my query. Once I took that line out everything worked quite well.
Jim
|||Please address future DTS (SSIS in SQL Server 2005) to the SSIS forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80
Thanks,
Jim
I figured it out...I had "USE (Name of Database)" in my query. Once I took that line out everything worked quite well.
Jim
|||Please address future DTS (SSIS in SQL Server 2005) to the SSIS forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80
Wednesday, March 21, 2012
DTS csv import fails when using double quotes as the Text Qualifier, the last column is a
I am using DTS to import csv files that I receive from a third party.
Each day I receive many file which I import into many different SQL
Server tables depending on the name of the file. I am doing this as
part of a larger automation process written in C#. Due to the csv
file names differing each day (they contain a date) and the large
number of DB Table schemas, I am programmatically/dynamically
creating/building a generic DTS package for each file and calling it
to import the csv file into the appropriate table.
The data contains commas for the column delimiters, double quotes for
the Text Qualifier and CR LF for the row delimiter. I have set the
appropriate DTSFlatFile connection properties and successfully
imported many of the csv files except files which meet the following
criteria:
1) the last column of the SQL Server table that corresponds to this
file is of type varchar or char
2) the data value for the last column of the record is always null
For these file I get the following error message when I try to execute
the package: "A DTSTransformCopy must specify no columns (signifying a
sequential 1-to-1 mapping of all columns) or the same number of source
and destination columns."
e.g.
File 1 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 2 -- imports fine:
--
"1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 3 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
File 4 -- fails:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
Table schema:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field3] [datetime] NOT NULL ,
[Field4] [datetime] NULL ,
[Field5] [datetime] NULL ,
[Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I am using SQL 2000 sp3a:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
BTW I found an 1.5 year old posting about this issue but no solution
was ever posted:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
Thanks,
DavidSo why you can't process this text file manually in your C# program
It will be a simple loop with output to the table
"David E Herbst" wrote:
> I am using DTS to import csv files that I receive from a third party.
> Each day I receive many file which I import into many different SQL
> Server tables depending on the name of the file. I am doing this as
> part of a larger automation process written in C#. Due to the csv
> file names differing each day (they contain a date) and the large
> number of DB Table schemas, I am programmatically/dynamically
> creating/building a generic DTS package for each file and calling it
> to import the csv file into the appropriate table.
> The data contains commas for the column delimiters, double quotes for
> the Text Qualifier and CR LF for the row delimiter. I have set the
> appropriate DTSFlatFile connection properties and successfully
> imported many of the csv files except files which meet the following
> criteria:
> 1) the last column of the SQL Server table that corresponds to this
> file is of type varchar or char
> 2) the data value for the last column of the record is always null
> For these file I get the following error message when I try to execute
> the package: "A DTSTransformCopy must specify no columns (signifying a
> sequential 1-to-1 mapping of all columns) or the same number of source
> and destination columns."
> e.g.
> File 1 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 2 -- imports fine:
> --
> "1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 3 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> File 4 -- fails:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> Table schema:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Table1]
> GO
> CREATE TABLE [dbo].[Table1] (
> [Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field3] [datetime] NOT NULL ,
> [Field4] [datetime] NULL ,
> [Field5] [datetime] NULL ,
> [Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I am using SQL 2000 sp3a:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
> BTW I found an 1.5 year old posting about this issue but no solution
> was ever posted:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
> Thanks,
> David
>|||Thank you for the suggestion.
Yes, that is an option but I am importing a many different csv files
to a large number of DB tables (each with a different number of
columns and column types) and I'm trying to avoid writing custom file
parsing code for each of the table schemas. Importing csv files seems
like a pretty standard problem that has been solved many times already
so I'm also trying to avoid spending time writing my own generic csv
file parser/loader which seems like reinventing the wheel. In
addition I've heard that large numbers of INSERTs though ADO.NET is
not that efficient and news groups postings usually suggest DTS
instead?
BTW I forgot to mention that I am taking advantage of the fact that
since the source and destination have the same number of columns in
the same order I don't have to define and add any column objects to
the DTS.DataPumpTransformCopy transformation object. This is useful
since I can use the same generic package creation code for all of the
DB tables and not embed any knowledge of the column schema in my code.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_45k3.asp?frame=true
(see comment in the example)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_976r.asp?frame=true
(first bullet points)sql
Each day I receive many file which I import into many different SQL
Server tables depending on the name of the file. I am doing this as
part of a larger automation process written in C#. Due to the csv
file names differing each day (they contain a date) and the large
number of DB Table schemas, I am programmatically/dynamically
creating/building a generic DTS package for each file and calling it
to import the csv file into the appropriate table.
The data contains commas for the column delimiters, double quotes for
the Text Qualifier and CR LF for the row delimiter. I have set the
appropriate DTSFlatFile connection properties and successfully
imported many of the csv files except files which meet the following
criteria:
1) the last column of the SQL Server table that corresponds to this
file is of type varchar or char
2) the data value for the last column of the record is always null
For these file I get the following error message when I try to execute
the package: "A DTSTransformCopy must specify no columns (signifying a
sequential 1-to-1 mapping of all columns) or the same number of source
and destination columns."
e.g.
File 1 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 2 -- imports fine:
--
"1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 3 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
File 4 -- fails:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
Table schema:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field3] [datetime] NOT NULL ,
[Field4] [datetime] NULL ,
[Field5] [datetime] NULL ,
[Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I am using SQL 2000 sp3a:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
BTW I found an 1.5 year old posting about this issue but no solution
was ever posted:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
Thanks,
DavidSo why you can't process this text file manually in your C# program
It will be a simple loop with output to the table
"David E Herbst" wrote:
> I am using DTS to import csv files that I receive from a third party.
> Each day I receive many file which I import into many different SQL
> Server tables depending on the name of the file. I am doing this as
> part of a larger automation process written in C#. Due to the csv
> file names differing each day (they contain a date) and the large
> number of DB Table schemas, I am programmatically/dynamically
> creating/building a generic DTS package for each file and calling it
> to import the csv file into the appropriate table.
> The data contains commas for the column delimiters, double quotes for
> the Text Qualifier and CR LF for the row delimiter. I have set the
> appropriate DTSFlatFile connection properties and successfully
> imported many of the csv files except files which meet the following
> criteria:
> 1) the last column of the SQL Server table that corresponds to this
> file is of type varchar or char
> 2) the data value for the last column of the record is always null
> For these file I get the following error message when I try to execute
> the package: "A DTSTransformCopy must specify no columns (signifying a
> sequential 1-to-1 mapping of all columns) or the same number of source
> and destination columns."
> e.g.
> File 1 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 2 -- imports fine:
> --
> "1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 3 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> File 4 -- fails:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> Table schema:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Table1]
> GO
> CREATE TABLE [dbo].[Table1] (
> [Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field3] [datetime] NOT NULL ,
> [Field4] [datetime] NULL ,
> [Field5] [datetime] NULL ,
> [Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I am using SQL 2000 sp3a:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
> BTW I found an 1.5 year old posting about this issue but no solution
> was ever posted:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
> Thanks,
> David
>|||Thank you for the suggestion.
Yes, that is an option but I am importing a many different csv files
to a large number of DB tables (each with a different number of
columns and column types) and I'm trying to avoid writing custom file
parsing code for each of the table schemas. Importing csv files seems
like a pretty standard problem that has been solved many times already
so I'm also trying to avoid spending time writing my own generic csv
file parser/loader which seems like reinventing the wheel. In
addition I've heard that large numbers of INSERTs though ADO.NET is
not that efficient and news groups postings usually suggest DTS
instead?
BTW I forgot to mention that I am taking advantage of the fact that
since the source and destination have the same number of columns in
the same order I don't have to define and add any column objects to
the DTS.DataPumpTransformCopy transformation object. This is useful
since I can use the same generic package creation code for all of the
DB tables and not embed any knowledge of the column schema in my code.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_45k3.asp?frame=true
(see comment in the example)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_976r.asp?frame=true
(first bullet points)sql
dts conversion error
I have a dts package that imports data from a comma delimited .csv file.
I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).
So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.
How can I make the conversion correctly during the dts execution.
Thank you.do you have any empty strings in the source file going to the float column?|||Also - does your CSV include column headings and, if so, have you ticked the box in the DTS to let SQL Server know that?sql
I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).
So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.
How can I make the conversion correctly during the dts execution.
Thank you.do you have any empty strings in the source file going to the float column?|||Also - does your CSV include column headings and, if so, have you ticked the box in the DTS to let SQL Server know that?sql
Monday, March 19, 2012
DTS and SP issue
I have a DTS that export a CSV file, and the sql is a store procedure(SP).
The DTS work fine if the SP is a single select statement. However, if there
are insert statements within the SP, the DTS failed to execute. How can i
solve this problem?
If I change the SP to sql function, it works. Unfortunately, I have a update
statement to be execute so that i can't change SP to sql function.
Thanks in advance!
MartinHi
I amn not quire sure what you are trying to do, but here are a few things
you may want to try!
Remove temporary tables and use derived tables or functions.
Use a CASE statement instead of updating the table.
SET NOCOUNT ON at the start of the procedure.
Split the job into several steps to do the pre-processing first
John
"Atenza" wrote:
> I have a DTS that export a CSV file, and the sql is a store procedure(SP).
> The DTS work fine if the SP is a single select statement. However, if ther
e
> are insert statements within the SP, the DTS failed to execute. How can i
> solve this problem?
> If I change the SP to sql function, it works. Unfortunately, I have a upda
te
> statement to be execute so that i can't change SP to sql function.
> Thanks in advance!
> Martin
>
>|||oh, it works now! Thank you so so much!!!!! Thank you for your support!!!
Martin
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:FA99D8E2-72F3-45C5-B40C-C3F2F648CD0E@.microsoft.com...
> Hi
> I amn not quire sure what you are trying to do, but here are a few things
> you may want to try!
> Remove temporary tables and use derived tables or functions.
> Use a CASE statement instead of updating the table.
> SET NOCOUNT ON at the start of the procedure.
> Split the job into several steps to do the pre-processing first
> John
> "Atenza" wrote:
>
procedure(SP).
there
i
update|||Hi
For the record, which option(s) made it work?
John|||SET NOCOUNT ON at the start of the procedure. Thx again!
Martin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113546952.977706.271200@.l41g2000cwc.googlegroups.com...
> Hi
> For the record, which option(s) made it work?
> John
>
The DTS work fine if the SP is a single select statement. However, if there
are insert statements within the SP, the DTS failed to execute. How can i
solve this problem?
If I change the SP to sql function, it works. Unfortunately, I have a update
statement to be execute so that i can't change SP to sql function.
Thanks in advance!
MartinHi
I amn not quire sure what you are trying to do, but here are a few things
you may want to try!
Remove temporary tables and use derived tables or functions.
Use a CASE statement instead of updating the table.
SET NOCOUNT ON at the start of the procedure.
Split the job into several steps to do the pre-processing first
John
"Atenza" wrote:
> I have a DTS that export a CSV file, and the sql is a store procedure(SP).
> The DTS work fine if the SP is a single select statement. However, if ther
e
> are insert statements within the SP, the DTS failed to execute. How can i
> solve this problem?
> If I change the SP to sql function, it works. Unfortunately, I have a upda
te
> statement to be execute so that i can't change SP to sql function.
> Thanks in advance!
> Martin
>
>|||oh, it works now! Thank you so so much!!!!! Thank you for your support!!!
Martin
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:FA99D8E2-72F3-45C5-B40C-C3F2F648CD0E@.microsoft.com...
> Hi
> I amn not quire sure what you are trying to do, but here are a few things
> you may want to try!
> Remove temporary tables and use derived tables or functions.
> Use a CASE statement instead of updating the table.
> SET NOCOUNT ON at the start of the procedure.
> Split the job into several steps to do the pre-processing first
> John
> "Atenza" wrote:
>
procedure(SP).
there
i
update|||Hi
For the record, which option(s) made it work?
John|||SET NOCOUNT ON at the start of the procedure. Thx again!
Martin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113546952.977706.271200@.l41g2000cwc.googlegroups.com...
> Hi
> For the record, which option(s) made it work?
> John
>
Friday, March 9, 2012
DTS + PK
Hello,
I have a table with a primary key.
I also have a CSV. The table has all the same fields as the CSV plus the
PKID.
I want to create a DTS that will import it. It seems to be putting in NULLS
for the PKID though.
I created the table and checked it. It is a PK and Identity is set to 1 and
increment by 1.
How to fix this?When you are in the DTS wizard, click the transform tab and see if all
columns are properly matched.
"Won Lee" <noemail> wrote in message
news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a primary key.
> I also have a CSV. The table has all the same fields as the CSV plus the
> PKID.
> I want to create a DTS that will import it. It seems to be putting in
NULLS
> for the PKID though.
> I created the table and checked it. It is a PK and Identity is set to 1
and
> increment by 1.
> How to fix this?
>|||All the columns are matched. I remember not having a problem with this at
all before. I have several other DTS packages that look similar. All the
cloumns are matched except for the pkID.
SO the table in SQL server has 7 fields. The CSV has 6 fields seperated by
commas.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> When you are in the DTS wizard, click the transform tab and see if all
> columns are properly matched.
>
> "Won Lee" <noemail> wrote in message
> news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I have a table with a primary key.
> > I also have a CSV. The table has all the same fields as the CSV plus
the
> > PKID.
> > I want to create a DTS that will import it. It seems to be putting in
> NULLS
> > for the PKID though.
> > I created the table and checked it. It is a PK and Identity is set to 1
> and
> > increment by 1.
> >
> > How to fix this?
> >
> >
>|||Very strange. However, when you uncheck Allow Identity Insert, it looks
working -- though I thought it should be the other way around.
"Won Lee" <noemail> wrote in message
news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> All the columns are matched. I remember not having a problem with this at
> all before. I have several other DTS packages that look similar. All the
> cloumns are matched except for the pkID.
> SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
by
> commas.
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > When you are in the DTS wizard, click the transform tab and see if all
> > columns are properly matched.
> >
> >
> > "Won Lee" <noemail> wrote in message
> > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I have a table with a primary key.
> > > I also have a CSV. The table has all the same fields as the CSV plus
> the
> > > PKID.
> > > I want to create a DTS that will import it. It seems to be putting in
> > NULLS
> > > for the PKID though.
> > > I created the table and checked it. It is a PK and Identity is set to
1
> > and
> > > increment by 1.
> > >
> > > How to fix this?
> > >
> > >
> >
> >
>|||Yes. That was the case. I delselctged enable indentity insert and it
worked fine.
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:eGaoByWUDHA.2316@.TK2MSFTNGP09.phx.gbl...
> Very strange. However, when you uncheck Allow Identity Insert, it looks
> working -- though I thought it should be the other way around.
>
> "Won Lee" <noemail> wrote in message
> news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> > All the columns are matched. I remember not having a problem with this
at
> > all before. I have several other DTS packages that look similar. All
the
> > cloumns are matched except for the pkID.
> >
> > SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
> by
> > commas.
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > > When you are in the DTS wizard, click the transform tab and see if all
> > > columns are properly matched.
> > >
> > >
> > > "Won Lee" <noemail> wrote in message
> > > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > I have a table with a primary key.
> > > > I also have a CSV. The table has all the same fields as the CSV
plus
> > the
> > > > PKID.
> > > > I want to create a DTS that will import it. It seems to be putting
in
> > > NULLS
> > > > for the PKID though.
> > > > I created the table and checked it. It is a PK and Identity is set
to
> 1
> > > and
> > > > increment by 1.
> > > >
> > > > How to fix this?
> > > >
> > > >
> > >
> > >
> >
> >
>
I have a table with a primary key.
I also have a CSV. The table has all the same fields as the CSV plus the
PKID.
I want to create a DTS that will import it. It seems to be putting in NULLS
for the PKID though.
I created the table and checked it. It is a PK and Identity is set to 1 and
increment by 1.
How to fix this?When you are in the DTS wizard, click the transform tab and see if all
columns are properly matched.
"Won Lee" <noemail> wrote in message
news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a primary key.
> I also have a CSV. The table has all the same fields as the CSV plus the
> PKID.
> I want to create a DTS that will import it. It seems to be putting in
NULLS
> for the PKID though.
> I created the table and checked it. It is a PK and Identity is set to 1
and
> increment by 1.
> How to fix this?
>|||All the columns are matched. I remember not having a problem with this at
all before. I have several other DTS packages that look similar. All the
cloumns are matched except for the pkID.
SO the table in SQL server has 7 fields. The CSV has 6 fields seperated by
commas.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> When you are in the DTS wizard, click the transform tab and see if all
> columns are properly matched.
>
> "Won Lee" <noemail> wrote in message
> news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I have a table with a primary key.
> > I also have a CSV. The table has all the same fields as the CSV plus
the
> > PKID.
> > I want to create a DTS that will import it. It seems to be putting in
> NULLS
> > for the PKID though.
> > I created the table and checked it. It is a PK and Identity is set to 1
> and
> > increment by 1.
> >
> > How to fix this?
> >
> >
>|||Very strange. However, when you uncheck Allow Identity Insert, it looks
working -- though I thought it should be the other way around.
"Won Lee" <noemail> wrote in message
news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> All the columns are matched. I remember not having a problem with this at
> all before. I have several other DTS packages that look similar. All the
> cloumns are matched except for the pkID.
> SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
by
> commas.
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > When you are in the DTS wizard, click the transform tab and see if all
> > columns are properly matched.
> >
> >
> > "Won Lee" <noemail> wrote in message
> > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I have a table with a primary key.
> > > I also have a CSV. The table has all the same fields as the CSV plus
> the
> > > PKID.
> > > I want to create a DTS that will import it. It seems to be putting in
> > NULLS
> > > for the PKID though.
> > > I created the table and checked it. It is a PK and Identity is set to
1
> > and
> > > increment by 1.
> > >
> > > How to fix this?
> > >
> > >
> >
> >
>|||Yes. That was the case. I delselctged enable indentity insert and it
worked fine.
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:eGaoByWUDHA.2316@.TK2MSFTNGP09.phx.gbl...
> Very strange. However, when you uncheck Allow Identity Insert, it looks
> working -- though I thought it should be the other way around.
>
> "Won Lee" <noemail> wrote in message
> news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> > All the columns are matched. I remember not having a problem with this
at
> > all before. I have several other DTS packages that look similar. All
the
> > cloumns are matched except for the pkID.
> >
> > SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
> by
> > commas.
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > > When you are in the DTS wizard, click the transform tab and see if all
> > > columns are properly matched.
> > >
> > >
> > > "Won Lee" <noemail> wrote in message
> > > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > I have a table with a primary key.
> > > > I also have a CSV. The table has all the same fields as the CSV
plus
> > the
> > > > PKID.
> > > > I want to create a DTS that will import it. It seems to be putting
in
> > > NULLS
> > > > for the PKID though.
> > > > I created the table and checked it. It is a PK and Identity is set
to
> 1
> > > and
> > > > increment by 1.
> > > >
> > > > How to fix this?
> > > >
> > > >
> > >
> > >
> >
> >
>
dts : how to process a varying list of csv files ?
Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSE S#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSE S#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
dts : how to process a varying list of csv files ?
Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725
dts : how to process a varying list of csv files ?
Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file withi
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Dat
Source="e:\tst.csv";Extende
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you ar
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can'
find a good help on the web
any one can help ?
Thanks in advance
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672Have a look at
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dominique deboise" <dominique.deboise.1r1e0t@.no-mx.msusenet.com> wrote in
message news:dominique.deboise.1r1e0t@.no-mx.msusenet.com...
> Hi all,
> I need to upload multiple csv files into one dts.
> 1)
> Is there a simple standard way to retrieve the list of csv file within
> the dts ?
> 2)
> to process the csv files (various file name) I tried to use
> opendatasource (as I did for excel files) :
> select * from
> OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source="e:\tst.csv";Extended
> properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
>
> Unfortunatly, I does not work , the message is :
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> my path is correct, it might be a syntax & parameter error but I can't
> find a good help on the web
> any one can help ?
>
> Thanks in advance
>
> Dominique
>
> --
> dominique deboise
> ---
> dominique deboise's Profile:
> http://www.msusenet.com/member.php?userid=2231
> View this thread: http://www.msusenet.com/t-1870556725
>|||I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file withi
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Dat
Source="e:\tst.csv";Extende
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you ar
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can'
find a good help on the web
any one can help ?
Thanks in advance
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672Have a look at
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dominique deboise" <dominique.deboise.1r1e0t@.no-mx.msusenet.com> wrote in
message news:dominique.deboise.1r1e0t@.no-mx.msusenet.com...
> Hi all,
> I need to upload multiple csv files into one dts.
> 1)
> Is there a simple standard way to retrieve the list of csv file within
> the dts ?
> 2)
> to process the csv files (various file name) I tried to use
> opendatasource (as I did for excel files) :
> select * from
> OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source="e:\tst.csv";Extended
> properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
>
> Unfortunatly, I does not work , the message is :
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> my path is correct, it might be a syntax & parameter error but I can't
> find a good help on the web
> any one can help ?
>
> Thanks in advance
>
> Dominique
>
> --
> dominique deboise
> ---
> dominique deboise's Profile:
> http://www.msusenet.com/member.php?userid=2231
> View this thread: http://www.msusenet.com/t-1870556725
>|||I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672
Wednesday, March 7, 2012
DTS "Can Not Insert Null Value"
Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
DTS "Can Not Insert Null Value"
Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.
DTS - problem on after inserting .csv file into sql database
Hello,
I inserted a .csv file into the database using DTS and it get inserted successfully.
But my problem is, the csv file contains the datas that has double quotes on it (eg: 1/2" iron rod, 3/4" wooden rod).
When I checked the inserted datas, it was inserted as "1/2" iron rod, 3/4"""," 3/4" wooden rod"".
How do I eliminate these additional double quotes.
Raviraj Danasekaran
The pattern of what it did (per your example) doesn't make sense to me.
That said, one way would be to update the affected columns using a replace function.
update my_table
set my_problem_column = replace(my_problem_column,'""','"')
(that's a single quote followed by two double quotes followed by a single quote, then a comma, then a single quote followed by a double quote and a single quote.)
That's a brute force method, but it might work to fix the problem if it's a one-time thing.
You might need to adjust your dts packages, there might be some values you can set that would change the quote handling behavior.
DTS - Import CSV
Okay guys im having a problem... i've created a DTS package to import a CSV file, but all fields containing integers shoot me an error stating that it can't insert a STRING into an INTEGER column...
Does anybody know how to circumvent this? I've googled it to death and still can't come up with a solution. Maybe i'm asking for the wrong thing... who knows... PLEASE HELP!!!Would it be better if i just imported an xls or mdb or someething?|||Hi,
you might want to open the .csv file in Excel and check if the particular columns that are causing the problem are not of text type.
Cheers,
Ziyi|||every entry in this colum is integer... meaning no characters, just numbers...
Subscribe to:
Posts (Atom)