Hi all,
We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.
DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.
TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.
-PatP
Showing posts with label source. Show all posts
Showing posts with label source. Show all posts
Thursday, March 29, 2012
DTS Rounding problem
I have a problem...
Data transformation rounds or truncate decimals!
I have an ODBC source in witch is a table with float numbers (ODBC Driver
publish it as float).
If I build a query form access or from excel with Query Analizer, I see all
decimal places, but when I try to insert data using DTS, float numbers will
be converted to its integer values.
A "select * from table", with table ODBC table, gives integer value when
runned from DTS to transform data from ODBC to MS-SQL Server table, and
gives float values from Access or other tools.
Where I can fix my problem?
Thanks.
Giorgioput convert(float,field) around your fields in the insert
example
declare @.i int
select @.i =1
select convert(float,@.i),@.i
Data transformation rounds or truncate decimals!
I have an ODBC source in witch is a table with float numbers (ODBC Driver
publish it as float).
If I build a query form access or from excel with Query Analizer, I see all
decimal places, but when I try to insert data using DTS, float numbers will
be converted to its integer values.
A "select * from table", with table ODBC table, gives integer value when
runned from DTS to transform data from ODBC to MS-SQL Server table, and
gives float values from Access or other tools.
Where I can fix my problem?
Thanks.
Giorgioput convert(float,field) around your fields in the insert
example
declare @.i int
select @.i =1
select convert(float,@.i),@.i
http://sqlservercode.blogspot.com/
DTS return
I am trying to execute a DTS package which will update four tables on a central server from various souces (each source will insert records into all four tables).
We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.
SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr
I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.
The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB
Any thoughts?You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.
Originally posted by achorozy
You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?sqlsql
We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.
SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr
I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.
The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB
Any thoughts?You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.
Originally posted by achorozy
You should take a look at this article:
Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)
Have you thought about executing DTS from VB?sqlsql
Sunday, March 25, 2012
DTS permissions
I would like to import data from Source database into Target using DTS, what are the minimum SQL Server 2K privileges/server roles required for regular user to achieve that on both Source and Target instances? This import will replace existing objects on Target.
Thanks
As a client side tool, the minimum needed would be the same as if you were executing the SQL statements from any other tool. You'd need permissions to connect to the source, access the database, read from the table/view or execute whatever stored procedure. On the target, you'd need to be able to drop and create whatever objects are involved, insert into the table, etc.
-Sue
|||There are also mulitple ways of doing this - if you're talking about transferring data between SQL Server databases, then Sue is correct. You can also export the data from the source server and store it as a file to be imported into the target. In that case, you have a lot of options, from file permissions to FTP security.
|||I was trying to use DTS in order to copy database objects from source to target, using drop target objects before copy option. In both databases user was db_owner however when I tried using DTS it was giving error saying that I need to have sysadmin privileges and sure enough as soon as I granted that to a user it worked. I also think that is overkill because I don't want developers working with DTS packages getting almighty privs.
Wednesday, March 21, 2012
DTS Package: Data Loading ?
Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve
DTS Package: Data Loading ?
Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve
Sunday, March 11, 2012
DTS Package Error - Something simple?
I have a multi-step DTS package that I get the following error when I try to execute it:
Package Error
Error Source : microsoft ole db provider for sql server
Error Description : [DBNETLIB][ConnectionOpen (connect()).]SQL Server does not exist or access denied
Package Error
Error Source : microsoft ole db provider for sql server
Error Description : [DBNETLIB][ConnectionOpen (connect()).]SQL Server does not exist or access denied
Am I missing something obvious? I'm sure it is something simple but I can't seem to get it. Thank you in advance for you assistance.It means the package can't connect to a data source it wants to connect to. Open up the package in Design mode and look at the connection properties. Check that the server name, the authentication method, and the username and password are correct.
DTS package elements at run time through VB6
How can i configure DTS package elements at run time through VB6..?
Like Specifying the Source,Destination etcTry to start http://search.microsoft.com/search/results.aspx?View=msdn&st=a&qu=SQL-DMO&c=0&s=1
Like Specifying the Source,Destination etcTry to start http://search.microsoft.com/search/results.aspx?View=msdn&st=a&qu=SQL-DMO&c=0&s=1
Wednesday, March 7, 2012
DTS Package
Hi,
I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?
Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.
I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?
Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.
DTS only copies first row
Hi,
I'm trying to copy results from a view on one table to another table in a different db using DTS in SQL Server Management Studio
Both source and destination db's are version 8.0.194
Everything reports as working, but when I check the results, only one row has copied - DTS reports that 1167 rows were copied.
This happens to different sources and destinations so I doubt it's my query.
When I perfrom the same DTS task using the old enterprise manager DTS in SQL Server 2000, everything copies fine.
Obviously one answer is "Use SQL Server 2000 Enterprise Manager then. 
Any ideas?
I don't know what the problem may be but you need to identify exactly where the problem is occurring. My first avenue of investigation would be to put a data viewer immediately prior to your destiantion to see how many rows are flowing into it. Check that the number displayed in teh GUI is correct.
-Jamie
Sunday, February 26, 2012
DTS migration wizard, where does it put migrated version?
From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.
Thanks,
John
In Sql Management Studio, under the Object Explorer window in the Connect drop down choose Integration Services, and it will show you all stored packages on that instance.|||Thanks, this helps. Now I'd like to find out how to edit the migrated version. How to I get to it from BI Dev Studio?|||All of the stored packages will usually be in ~\Program Files\Microsoft SQL Server\90\DTS\Packages
Friday, February 24, 2012
DTS Job Runs Forever
I can manually run a DTS package from SQL Server 2000 where the data
source is an Oracle database. Run it manually and it completes in a
few seconds. When I try to run it as a job it runs forever.
I read other posts suggesting that the SQLAgentCmdExec needs to be a
user on the Oracle server (Sun box). Why is that if the ODBC
connection already authenticates the connection? This is what is
confusing IAW Q269074.
Thanks for any suggestions.
RBollingerThe most common causes of a package running ok manually but failing as a job
are either security or environment related. When you run the package
manually, are you doing so on your PC or on the SQL Server (directly or via
Remote Desktop)? The Windows security context isn't usually an issue for
the Oracle connection, although it might be a factor for other resources
need by the package.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"robboll" <robboll@.hotmail.com> wrote in message
news:1159215526.092875.48620@.i3g2000cwc.googlegroups.com...
>I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
>|||I found the solution:
To correct the problem I installed SQL Server Service Pack 4. That's
it.
robboll wrote:
> I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
source is an Oracle database. Run it manually and it completes in a
few seconds. When I try to run it as a job it runs forever.
I read other posts suggesting that the SQLAgentCmdExec needs to be a
user on the Oracle server (Sun box). Why is that if the ODBC
connection already authenticates the connection? This is what is
confusing IAW Q269074.
Thanks for any suggestions.
RBollingerThe most common causes of a package running ok manually but failing as a job
are either security or environment related. When you run the package
manually, are you doing so on your PC or on the SQL Server (directly or via
Remote Desktop)? The Windows security context isn't usually an issue for
the Oracle connection, although it might be a factor for other resources
need by the package.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"robboll" <robboll@.hotmail.com> wrote in message
news:1159215526.092875.48620@.i3g2000cwc.googlegroups.com...
>I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
>|||I found the solution:
To correct the problem I installed SQL Server Service Pack 4. That's
it.
robboll wrote:
> I can manually run a DTS package from SQL Server 2000 where the data
> source is an Oracle database. Run it manually and it completes in a
> few seconds. When I try to run it as a job it runs forever.
> I read other posts suggesting that the SQLAgentCmdExec needs to be a
> user on the Oracle server (Sun box). Why is that if the ODBC
> connection already authenticates the connection? This is what is
> confusing IAW Q269074.
> Thanks for any suggestions.
> RBollinger
DTS issue
In Microsoft SQL DTS I'm trying to run a package that convert output to a excel spreadsheet. Following error appears:
Error source: Microsoft Jet Database Engine
Error Description: Failure Creating File. What has added to my frustrations is, once I go to the server and open up the DTS package and click on destination it allows me to create new table, but that is only temporary. If I run a schedule job at a later time it fails again with the above message.Well if you're doing a CREATE, have you added the DROP?|||Brett, thanks for your reponse. It's only creating a dynamic table through DTS. When I take a look at the tables list it does not exist.|||Take a look at this post -> should answer your question.
http://www.dbforums.com/showthread.php?threadid=981661
kbk
Error source: Microsoft Jet Database Engine
Error Description: Failure Creating File. What has added to my frustrations is, once I go to the server and open up the DTS package and click on destination it allows me to create new table, but that is only temporary. If I run a schedule job at a later time it fails again with the above message.Well if you're doing a CREATE, have you added the DROP?|||Brett, thanks for your reponse. It's only creating a dynamic table through DTS. When I take a look at the tables list it does not exist.|||Take a look at this post -> should answer your question.
http://www.dbforums.com/showthread.php?threadid=981661
kbk
Sunday, February 19, 2012
DTS Insert or Update
Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.
I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.
i think your going to have to use VBScript in the transformation step of the DTS Package to evaluate if the record exists. Similar to what one would do in the stored proc.
hth,
mcm
DTS in parallel
I have a DTS package I created that is dadabase driven to process source data from multiple sources.
Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?
JavaWaba
Hello,
Firstly, you can execute DTS packages in parallel by creating a new DTS package and and creating several Execute DTS Package tasks (each of these tasks can be created from the same DTS package). You can then specify the number of these tasks (DTS Packages in this case) to execute in parallel in the package properties (set to 4 by default).
You can then set the data source of each of these packages (now just tasks within a parent dts package) either via global variables, dynamic properties or similar.
Cheers,
Rob
Friday, February 17, 2012
DTS import: Disabling trigger and Constrains before import ?
Hi,
Is it a good idea to disable trigger and contrains in the source Database
before using DTS import
and enable trigger and contrains in the destination database after the
import has been completed ?
Thank you
DannyDanny,
Disabling triggers and constraints is not a good idea unless you know your
data and load process well enough to be sure that you will not be subverting
the reasons that the triggers and constraints were created.
But, if you know your import logic is good, then "Yes" you will receive some
load benefits by dropping or disabling the triggers and constraints in the
_target_ database until the load is complete, then reenable (or recreate)
them. (There is no point in disabling these in the source database.) You
could ask the same question about indexes, dropping them before the load and
recreating afterward.
The other factor is the amount of data being imported. If you are bringing
in a fraction of a percent in a daily update, then you may not want to do
any of this. If you are totally rebuilding the contents of the database,
then simplifying what the load must do is a good thing.
Your mileage may vary.
Russell Fields
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:u9#OZDz6DHA.632@.TK2MSFTNGP12.phx.gbl...
Is it a good idea to disable trigger and contrains in the source Database
before using DTS import
and enable trigger and contrains in the destination database after the
import has been completed ?
Thank you
DannyDanny,
Disabling triggers and constraints is not a good idea unless you know your
data and load process well enough to be sure that you will not be subverting
the reasons that the triggers and constraints were created.
But, if you know your import logic is good, then "Yes" you will receive some
load benefits by dropping or disabling the triggers and constraints in the
_target_ database until the load is complete, then reenable (or recreate)
them. (There is no point in disabling these in the source database.) You
could ask the same question about indexes, dropping them before the load and
recreating afterward.
The other factor is the amount of data being imported. If you are bringing
in a fraction of a percent in a daily update, then you may not want to do
any of this. If you are totally rebuilding the contents of the database,
then simplifying what the load must do is a good thing.
Your mileage may vary.
Russell Fields
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:u9#OZDz6DHA.632@.TK2MSFTNGP12.phx.gbl...
quote:
> Hi,
> Is it a good idea to disable trigger and contrains in the source Database
> before using DTS import
> and enable trigger and contrains in the destination database after the
> import has been completed ?
> Thank you
> Danny
>
DTS import: Disabling trigger and Constrains before import ?
Hi,
Is it a good idea to disable trigger and contrains in the source Database
before using DTS import
and enable trigger and contrains in the destination database after the
import has been completed ?
Thank you
DannyDanny,
Disabling triggers and constraints is not a good idea unless you know your
data and load process well enough to be sure that you will not be subverting
the reasons that the triggers and constraints were created.
But, if you know your import logic is good, then "Yes" you will receive some
load benefits by dropping or disabling the triggers and constraints in the
_target_ database until the load is complete, then reenable (or recreate)
them. (There is no point in disabling these in the source database.) You
could ask the same question about indexes, dropping them before the load and
recreating afterward.
The other factor is the amount of data being imported. If you are bringing
in a fraction of a percent in a daily update, then you may not want to do
any of this. If you are totally rebuilding the contents of the database,
then simplifying what the load must do is a good thing.
Your mileage may vary.
Russell Fields
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:u9#OZDz6DHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it a good idea to disable trigger and contrains in the source Database
> before using DTS import
> and enable trigger and contrains in the destination database after the
> import has been completed ?
> Thank you
> Danny
>
Is it a good idea to disable trigger and contrains in the source Database
before using DTS import
and enable trigger and contrains in the destination database after the
import has been completed ?
Thank you
DannyDanny,
Disabling triggers and constraints is not a good idea unless you know your
data and load process well enough to be sure that you will not be subverting
the reasons that the triggers and constraints were created.
But, if you know your import logic is good, then "Yes" you will receive some
load benefits by dropping or disabling the triggers and constraints in the
_target_ database until the load is complete, then reenable (or recreate)
them. (There is no point in disabling these in the source database.) You
could ask the same question about indexes, dropping them before the load and
recreating afterward.
The other factor is the amount of data being imported. If you are bringing
in a fraction of a percent in a daily update, then you may not want to do
any of this. If you are totally rebuilding the contents of the database,
then simplifying what the load must do is a good thing.
Your mileage may vary.
Russell Fields
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:u9#OZDz6DHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it a good idea to disable trigger and contrains in the source Database
> before using DTS import
> and enable trigger and contrains in the destination database after the
> import has been completed ?
> Thank you
> Danny
>
DTS Import/Export Wizard database copy problem
Hi
I'm experiencing problems copying a SQL 2000 database from my host to a local SQL Server.
I choose the Data Source OK, using SQL Server Authentication and the username and password used to setup my hosted database.
Then I specify the destination, my local server. I'm using my sa account and password and creating a <new> database (doesn't matter if I use the same db name as the source or a different one, I'm still getting an error).
I then choose "Copy objects and data between SQL Server databases", then take all defaults on the "Select Objects to Copy screen", i.e. create destination objects drop objects first and include dependent objects); creat data (replacing existing data); Copy all objects and use default options.
The copy appears to be creating a script OK (runs up to 100%), then looks to start copying objects. It gets to approx 20% and reports that it "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". When I click on the Executing Package entry it tells me:
[Microsoft][ODBC SQL Server Driver][SQL Server]The login already has an account under a different user name.
I can't seem to find any reference to this error in my books or on Google (usually a great source of info). Can't help feeling I'm doing something stupid. Would be very, very greatful if someone out there could give me a pointer in the right direction.
Thanks,
Keith.On the "Select Objects to Copy screen" I would uncheck the "Use default options" box at the bottom, click "Options..." and uncheck "Copy database users and database roles". This should take care of the problem.
Terri
DTS import with dashes in source data
I'm trying to import numbers from an excel spreadsheet into a SQL table using a DTS package. I'm getting null values imported when my source data contains a dash (-). The data type for the source is text, and the datatype for the destinations is varchar(100). I've also tried a source data type of general. I'm doing a straight copy.
The source data might look 234-b87-456. Why would I be getting nulls when importing this data?
Thanks.
StumpedDid you check your transformation options and make sure that the columns line up correctly?|||I did. The column names in the source file are identical to the column names in the destinations table. I double-checked the mapping just to be sure all were correctly mapped.|||Try doing the same import but without the dashes in the columns.|||There are values within the same column that do not have dashes, and they import correctly. It is only the values with dashes that are not importing. All other values import correctly.
Thanks.
DTS import question
I have a database table that stores personnel records that is imported from another source. I have SSN as a unique key.
Everyday I import data from a text file via DTS that checks for any updates to my records. If the data is updated I update my records, if there is new records I just add them to my table. The problem is every once in a while a duplicate ssn may appear in the text file I import. This causes my import to fail obviously because of the unique key on SSN. How do I allow DTS to continue and just ignore the problematic SSN? Can I write the conflicting SSN or error to a file somewhere?
To configure logging for your package, right click on the package inDTS Designer, select 'Package Properties'. Go to 'Logging' tab. In thistab, specify the error file, as well as the SQL Server to which youwant to log the package execution. When logging to SQL Server, theinformation will be logged into sysdtspackagelog and sysdtssteplogtables in the msdb database.Taken from http://vyaskn.tripod.com/sql_server_dts_best_practices.htm troubleshooting section.
|||
"One way of doing this would be to create a duplicate loading table. On the
UNIQUE index specify WITHIGNORE_DUP_KEY.
This will allow you to load the rows and SQL server will only load a
distinct version of the row based on that index.
You then push the rows that are processed into the real table "
I got the above from the SQL Server news groups and that was exactly what I thought you should do. Run a search on DTS and IGNORE_DUP_KEY on Google . Hope this helps. For more DTS questions check out the link below.
http://www.sqldts.com
Subscribe to:
Posts (Atom)