DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql

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

http://sqlservercode.blogspot.com/

DTS re-uses old text file input

Hi All,

A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.

The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.

A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.

I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.

I tried mapping to a UNC to no avail.

Is it buffering the old file somewhere? I need help.

current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.

Thanks Satya,

I'm not sure what you mean by "old settings"? Here's what I do know.

Drive 'G' is mapped to \\sun001\data

The input file name is weekly_updates.txt and does not change.

My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.

It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?

DTS Question - copy table from SQL server to non-SQL server

I'm breaking into SQL 2000, and have completed a DTS package that imports and massages a file and creates a table with about 200M records.

Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.

I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.

Any tips to get me started will be appreciated.

Thanks,

RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).

Then define your source query (or table), your destination and any mapping changes you want to make.

Best of luck,

hmscott

...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql

DTS Question

Hi,

I made a DTS which appends data coming infrom a view to an exisiting table.So far no problem and all goes well.

I am facing a problem due to the format of the date that is coming in (getting appended) and while going through BOL, came across the following topic:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\howtosql.chm::/ht_dts_trns_97ou.htm

I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).

Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.

Many TIAHow exactly is your current DTS package appending the destination table ? Do you have some logic right in the transformation ?

Cheers,
-Kilka|||the only thing your diagram shows is that you have a transformation that has more source columns then destination columns and there is nothing wrong with that.

As for the appending of data, well that really depends on what you are doing prior to your transformation. Are you deleting the existing data or not? This isn't really part of the transformation itself...

DTS question

Ok, i have a set of foxpro tables and a SQL 2000 db.

i'm trying to move a table of data from the fox to the sql2000.

I'm using dts to point a connection to a particular table in fox, and setting the destination to my sql database.

i select 'create table' and it makes the table in sql for me.

i click on the transformations tab, and it sets up the transforms. no problem.

when i execute, i'm getting these generic errors ('cannot convert data type') or some such bs. the error does not tell me WHAT TRANSFORMATION or what source field is causing the problem.

so i manually went and eliminated each dts transform (one at a time) and ran till i found the offending dts transform.

so i figured this one out.

here's my question:

WHAT A PAIN IN THE BUTT! what can i do to get dts to tell me what friggin transform is the offending transform?

I've got several HUNDRED tables to convert yet, and if i have to do this again i'm going to jump off a tall building.

there's got to be a better way...When we moved our data from Visual FoxPro into SQL Server we used the Upsize Wizard from within Visual FoxPro. It was pretty painless (although I believe we had to add all of the free tables to a database). Would this work for you?

Terri

DTS Question

Hi -

I want to create a DTS to pull data from Text file to a SQL Server Table. The problem is i want to implement some Business Logic in it.

Like the process would involve 2 SQL tables and i want to basically have to update the table data based on the ID that i have to match through other table...like a join...

How is it possible..??

Thanks for any input...

You have two problems SQL Server will see null values because your data is a text file so you need to import the text file to a temp table before moving to your destination table. The second problem is minor if you are in SQL Server 2005 because you will not have the known restrictions and limitations of text data. I have answered this question many times, SQL Server Agent needs Admin permissions and you can use a stored proc to run the DTS package. Try the link below for sample DTS code and if you are in SQL Server 2005 you could get Integration Services code. Hope this helps.

http://www.sqldts.com

sqlsql

DTS Question

I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >

DTS Query Builder - Using nulls

I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.

I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.

Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.

Any help would be greatly appreciated.Maybe you should re-design your query using the "where exists" or "where not exists" construct

something like:

select a, b from table1
where not exists (select * from table2 where table1.key = table2.key)|||Thanks. That worked like a charm.

DTS QUERY AND HELP

I’ve not had much experience with DTS packages before.I am using SQL Server 2000 and I have a table I wish to update.I have a spreadsheet with the updated records – this does not include all records and does not include all columns within the table,

I just wanted to know if it is still possible to update certain fields without having to include all the records and all the columns e.g.

Table 1

IDNAMESURNAME

1TEDSMITH

2FREDPERRY

3JOEYOUNG

Spreadsheet

IDSURNAME

2CASH

3WILLIS

Result

IDNAMESURNAME

1TEDSMITH

2FREDCASH

3JOEYOUNG

And the million-dollar question – please could you provide the steps to do this?

Take a look at the Data Driven Query Component|||

Can anyone provide any more information on this for me?

Thanks

|||If you need th enext level of help thn its probably best to move this thread to the SSIS forum|||

Look for "Building a Data Driven Query" in SQL Server 2000 Book Online, also found here: http://msdn2.microsoft.com/en-us/library/aa933507(sql.80).aspx

You'll want to follow the first example: "In the first example, every source row leads to the update of a destination row. Only one query type, an Update query, is used."

sqlsql

DTS Problem

Hi there,
I have created a DTS that copies data from a table in SQL Server to a table
with the same structure, but in Oracle.
Lets suppose I have 100 rows in the origin table. If the DTS fails in he
middle of the operation, supposing the row 55 has an error, the previous
rows are copied to the Oracle table.
How can I now wich rows have been copied?
The DTS runs periodicaly, and I need to know wich rows are copied, so I can
block them on the SQL Server, so they can not be copied again to Oracle.
Can anybody help me? Thanks in advance.
Regards,
Marcothis is not the proper way. but it works for me.
Make it 2 step.
In the first step, put the -ve of the real value; If the dts succees,
select all the rows with -ve value in the field and put it back to positive.
If it abends, you know all the rows with -ve value came from unsuccessful
import.
you can try other methods like prefixing text field like say lastname with
"SQL-"+lname and select all the rows where lastname like "SQL-" and remove
that.sqlsql

DTS performance

Hi,

Can anyone advise me of a quick way to estimate the time taken by DTS to
import a table (24 columns x 700,000 rows) from JD Edwards (running on
AS400) into SQL Server (new table and no manipulation involved)?

Many thanks,

Steve"Steve McDonald" <ajones1@.nsw.bigpond.net.au> wrote in message news:<TBbRa.7157$wU5.924@.news-server.bigpond.net.au>...
> Hi,
> Can anyone advise me of a quick way to estimate the time taken by DTS to
> import a table (24 columns x 700,000 rows) from JD Edwards (running on
> AS400) into SQL Server (new table and no manipulation involved)?
> Many thanks,
> Steve

There are plenty of factors which may vary from one environment to
another, so the most reliable way to find out would simply be to test
it. In theory, you could work out the volume of data (average row
length x number of rows), then divide that by your network speed, add
some time for DTS overhead etc., but in practice I think you wouldn't
get a very accurate answer.

Simon

dts parameters, more values in one parameter

hy all,

i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).

i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...

select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')

... cause everytime i run the dts the operationdate field must be filtered on different date.

so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.

i used to try:

select * from {table_name} where operationedate in (?)

but it doesnt work!

any one can understand what i try to explain and even help me?

bye

nicola

Have you tried using a SSIS variable with an expression to build query dinamically?

There are a lot of post about it. You would need the set the EvaluateAsExpression porperty of the variable as TRUE and then build the expression. The expression will use another variable that has the coma delimited list of values with the dates you care about.

|||

Build two variables: varInClause (string) & varSQLStatement (string)

For the value of varInClause, you can type your dates including the quotes and commas: '20070101','20070205',...

Set EvaluateAsExpresstion to true on the varSQLStatement variable. In the expressions box, type this as your expression:

"select * from table_name where operationdate in (" + @.{User::varInClause} + ")"

Then, use the variable, varSQLStatement, in your OLE DB source.

|||

thank you very much for answered so soon

but maybe i'm still in trouble

i forgot you to say i'm working in SQL Server 2000

i think that is possible to use SSIS only in SQL Server 2005

i'm right or not?

bye

nicola

|||Oh, well, that is a very important piece of information because this is not a DTS forum. If you're working with DTS, you need to post that question on the DTS newsgroup.

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

thank you very much

i'm sorry for time you loose with my problem

bye

nicola

DTS packet size

I have a dts package that transfers data from a view into
a table structure on another server. Occassionly the
scheduled package will fail with the following error:

Error = -2147467259 (80004005) Error string:
[DBNETLIB][ConnectionRead (recv()).]General network error.
Check your network documentation. Error source:
Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider
Error: 11 (B) Error string: [DBNETLIB]
[ConnectionRead (recv()).]General network error. Check
your network documentation. Error source: Microsoft
OLE DB Provider for SQL Server Help file: Help
context: 0 DTSRun OnFinish: Copy Data from PS...
Process Exit Code 1. The step failed.

Once the package fails I will rerun it several times
without any modifications and eventually it will run to
completion successfully. One change that I have made while
troubleshooting is to change the default network package
size from 1 to 1024 or 2048. Although this has worked I'm
not confident that the package wouldn't have run without
that change.

Has anyone seen this error before or anything similar?Default network packet size is 4096. How did you manage to make it 1?|||Under the Data Link Connection tab the default packet size is 1. It gives you the option to Edit the value so I bumped it up to 2048.

DTS package to import a csv file on a web server

Im trying to schedule a dts package to import a database table from a csv
file.
In enterprise manager when i attempt to create the dts process, im not sure
what datasource should i be using to connect to my csv file that resides on
the web server?
where do i specify me server to connect to; eg: www.myserver.com\dbRight-click Local Packages and select New Package, Look on Connection and
select Text File (Source). Specify the file name location on the network.
Perhaps you will need to create a shared folder on your web server and give
permissions to the SQL Server service account.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> Im trying to schedule a dts package to import a database table from a csv
> file.
> In enterprise manager when i attempt to create the dts process, im not sur
e
> what datasource should i be using to connect to my csv file that resides o
n
> the web server?
> where do i specify me server to connect to; eg: www.myserver.com\db
>
>|||thanks ben
I think my biggest problem is working out what my UNC path to my web server
is to locate my text file
should it look something like this
\\syd2004\www.bradyaust.com.au\db\
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
> Right-click Local Packages and select New Package, Look on Connection and
> select Text File (Source). Specify the file name location on the network.
> Perhaps you will need to create a shared folder on your web server and
> give
> permissions to the SQL Server service account.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "bj" wrote:
>|||Go to your web server, select the folder containing the text file,
right-click and select Sharing and Security, Share this folder, give
Permissions (very careful here), maybe remove permissions to Everyone, Add
the SQL Server service account, etc.
Let us suppose you shared your folder as MyData, then SQL Server would be
able to access the file using \\webservername\MyData\MyTextFile.
Again, pay special attention to the permissions you give to the shared folde
r.
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> thanks ben
> I think my biggest problem is working out what my UNC path to my web serve
r
> is to locate my text file
> should it look something like this
> \\syd2004\www.bradyaust.com.au\db\
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
>
>

DTS package to check if table exists

Hi all,

I have created a DTS package in SQL Server that takes Excel file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

a lot of thanks in advance,

Dmitry

yeah, and by the way,

when i execute the DTS i can see how many records are taken from XLS and copied into the database, if i can just somehow check that number, retreive that varible... that would solve the problem.

thanks again,

Dmitry

|||

It is often a good idea to import data into 'holding tables' (duplicate schema to the production tables), do whatever data cleanup/manipulation is required, and then move the data to the final tables.

With that process, it is easy to determine rowcount, duplications, etc, before running other code.

|||

ok...sounds ok, i have never done them before though... can you link an example or something..

many thanks

Dts package that imports difference

Hi,
I have to server and two different database on them. I want to import one
table from database1 to database2. But I want to import only difference
between two table.I want to also keep identity column value. I created a dts
package but I don't know how can I put a condition like "where myid not
in(Select myid from database2.table)" and where can I put Set
IDENTITY_INSERT mytable ON?
Thanks in advance,Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>|||I don't think you can connect from "Execute SQL task" to both server. You
need to select connection(server) first. Am I wrong?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
> Assuming you are using an Execute SQL task, you can use a script like the
> one below:
>
> SET IDENTITY_INSERT Database2.dbo.MyTable ON
> GO
> INSERT INTO Database2.dbo.MyTable
> (
> ID,
> MyData
> )
> SELECT
> ID,
> MyData
> FROM Database1.dbo.MyTable AS t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM Database2.dbo.MyTable AS t2
> WHERE t2.ID = t1.ID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "italic" <hugur@.hotmail.com> wrote in message
> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import one
>> table from database1 to database2. But I want to import only difference
>> between two table.I want to also keep identity column value. I created a
>> dts package but I don't know how can I put a condition like "where myid
>> not in(Select myid from database2.table)" and where can I put Set
>> IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>|||in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||Hi
"italic" wrote:
> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John|||Linked Server is not allowed. I can try the second way.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE7E5676-71E5-4202-9ACB-BFEC91B180C8@.microsoft.com...
> Hi
> "italic" wrote:
>> I don't think you can connect from "Execute SQL task" to both server. You
>> need to select connection(server) first. Am I wrong?
>> Thanks
> You can create a linked server and reference that in your query, or as you
> have a second connection to the destination server you could use a query
> (task) to retrieve the highest identity from the destination server then
> use
> that in a global variable as the start of the information retrieved from
> the
> second server.
> See http://www.sqldts.com/280.aspx
> http://www.sqldts.com/205.aspx
> The second method would not necessarily be transactionally consistent,
> therefore you should not be changing the destination table when this is
> running.
>
> John|||Thanks for reply.
"Jeje" <willgart@.hotmail.com> wrote in message
news:ORGk$P8kHHA.5048@.TK2MSFTNGP04.phx.gbl...
> in DTS,
> first you have to get the lastet ID (select max(id) from destination)
> then you have to dynamically create the select statement (Select * from
> source where id > maxid)
> (or you have to use a parametrized query where the parameter will be the
> ID)
> and finally load the data.
>
>
> "italic" <hugur@.hotmail.com> wrote in message
> news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>>I don't think you can connect from "Execute SQL task" to both server. You
>>need to select connection(server) first. Am I wrong?
>> Thanks
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like
>> the one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>>|||Hi
"italic" wrote:
> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John|||I mean, company db admin don't want linked server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0C51877A-3573-4795-83F9-017AAA43B870@.microsoft.com...
> Hi
> "italic" wrote:
>> Linked Server is not allowed. I can try the second way.
>> Thanks
> You would need a 4 part name! What error do you get?
> John

DTS Package Problem

Created a DTS Package is SQL that drops a table then recreates it then
imports data from an excel spreadsheet. This works fine if you run it
from the Local Packages and click execute package.
But, if you run it using the xp_cmdshell it doesn't popluate all the
columns. It will fill the 29 columns then the rest are null and then it
will fill the last 4 columns.
I thought that maybe that if the 30th column is null in the first row
then the rest would be null. So I sorted by that column in the package
and I'm getting the same results.
Does a package run differently with the xp_cmdshell versus righ
clicking and executing?
Thanks
MikeIf you run a exectue sql task with xp_cmdshell in it by right clicking and
executing, this will run same as executing full package with other steps.
How are you running it exactly (from where, how) and what is the sql stmt
inside xp_cmdshell ?
"Mike" wrote:

> Created a DTS Package is SQL that drops a table then recreates it then
> imports data from an excel spreadsheet. This works fine if you run it
> from the Local Packages and click execute package.
> But, if you run it using the xp_cmdshell it doesn't popluate all the
> columns. It will fill the 29 columns then the rest are null and then it
> will fill the last 4 columns.
> I thought that maybe that if the 30th column is null in the first row
> then the rest would be null. So I sorted by that column in the package
> and I'm getting the same results.
> Does a package run differently with the xp_cmdshell versus righ
> clicking and executing?
> Thanks
> Mike
>|||EXEC master..xp_cmdshell 'dtsrun /Sservername /E /NExcelInsert'
running this statement leaves most columns NULL.
Going through Enterprise manager --> local packages and right clicking
and hitting execute runs correctly.
MattB wrote:
> If you run a exectue sql task with xp_cmdshell in it by right clicking and
> executing, this will run same as executing full package with other steps.
> How are you running it exactly (from where, how) and what is the sql stmt
> inside xp_cmdshell ?
>
> "Mike" wrote:
>

DTS Package Owner

Hello there,

Im trying to change the owership of the DTS package but am a little
confused.

sp_changeobjectowner changes the ownership of table, view, or stored
procedure in the current database...

How could i change the DTS package ownership?

Thank you in advance.

Leo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo

In most cases, it doesn't really matter who owns the package - who is
executing it is more important. However, there is an undocumented
stored procedure called msdb..sp_reassign_dtspackageowner, which you
can use.

You might want to look at http://www.sqldts.com - in the
Administration and Management section of the FAQ, there is an article
on package ownership issues, including a wrapper procedure for
sp_reassign_dtspackageowner.

Of course, since this procedure is undocumented, you should be careful
using it, and preferably never in production code - its behaviour may
change in future releases with no warning.

Simon|||Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Hi Leo, use the undocumented SP:

sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'

[@.name =] 'name'
The package name.

[@.id =] 'id'
This is the uniqueidentifier for the package. A name may not
necessarily be unique.

[@.newloginname =] 'newloginname'
The new Owner name. SQL Server login example 'sa', NT Integrated
example 'Domain\Username'

Regards,
Thomas
http://www.sqlscripter.com

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:
>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com