I created a DTS FTP task to connect to an FTP site. The problem I'm having is the folder I want to access you cannot see under the Files Tab. So if I put the ftp site/subdirectory in the FTP Site box, I get the error "Unable to connect to internet! Please check the source site information, your username and password"
If I go to the FTP site/subdirectory in IE all the files show up fine. I believe this is limitation of DTS, but I would like to hear from you all first.
To see what error I'm talking about:
Create a DTS FTP task
Use: ftp.microsoft.com/bussys/ with no username or password
Click the Files Tab
Normally you can just put the server name in, and see all the folders/files, but the way my FTP site is set up I cannot see the folder I want, I can only access it.What do you see in the Address field of the browser when you click on this site? Something like this?
ftp://<user_id>:<password>@.ftp_site.com
If this is the case then that's the reason why you can see files and folders using your browser vs. DTS package designer.
You NEED to specify user_id and password to access FTP sites unless they are set up to allow anonymous access.
Exception to it would be a setting on an FTP site where browsing is not allowed while accessing a specific file is. Consult with the FTP site admin.
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Thursday, March 29, 2012
DTS File Created After Stored Proc executed now what?
I'm trying to fire a DTS package through a stored procedure.
After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.
How would I use the DTS file to update my data.
Thanks,
RB
RB,
There is no direct way to do this via a SP. In my experience, I've seen three ways to work around this:
1) Use xp_cmdshell. This simply executes a command in the command shell. Usefull if you already have a batch script running your DTS.
2) Use OLE. You can create and work with Com objects via the sp_OA* methods. You will need to create the DTS objects and run properties from them.
3) Use a scheduled Job. Saw this recently, the idea is to create a new scheduled job that runs your DTS, then run the job, then delete the scheduled job.
None are pretty, but they all work.
-- Alex
Tuesday, March 27, 2012
DTS failed to run automaticlly
Hello,
I created a DTS to transfer data from one SQL server to another SQL server.
However, I can only run the job manually. If I scheduled this job to be run
everyday automaticlly, It failed.
Please help.
Thanks.
FuhlSounds like a permissioning problem. When you manually run the package it executes using your username and permisions when it executes as a job I believe it runs as the SQL Agent (not certain on this but it is definately not using your permissions).
HTH|||rokslide is right.
When the dts or jobs run automatically they use SQL Server Agents startup accounts credentials. Just that that the SQL Agent startup account has permissions to do the job what you have put in the DTS.
Also is should have permission to write on the other server don't forget that.
I created a DTS to transfer data from one SQL server to another SQL server.
However, I can only run the job manually. If I scheduled this job to be run
everyday automaticlly, It failed.
Please help.
Thanks.
FuhlSounds like a permissioning problem. When you manually run the package it executes using your username and permisions when it executes as a job I believe it runs as the SQL Agent (not certain on this but it is definately not using your permissions).
HTH|||rokslide is right.
When the dts or jobs run automatically they use SQL Server Agents startup accounts credentials. Just that that the SQL Agent startup account has permissions to do the job what you have put in the DTS.
Also is should have permission to write on the other server don't forget that.
DTS Export to Excel
I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I am using a DTS created by the Export Wizard to send data to an Excel
> > spreadsheet. The problem is that each time the DTS is run, the data gets
> > appended to the spreadsheet, rather than replaced. I used the Drop and
> > Create Destination Table option on the Transformation window.
> >
> > I found an earlier post regarding this same problem. The suggestion was to
> > use the Delete option in the Transformation window of the wizard. I rebuilt
> > the DTS based on that idea, but get an error message about "Deleting data in
> > a linked table is not supported by this ISAM".
> >
> > Can someone offer some ideas on how to get the data replaced in the Excel
> > spreadsheet?
> >
> > Thanks.
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I cannot rename the spreadsheet because it is tied into other processes. I
> > need to replace the data that already exists in the spreadsheet.
> >
> > If it helps, I did some research since my original posting and here is what
> > I found:
> > 1) When DTS creates the range name in the spreadsheet, it is only the
> > headings of the data. The data itself is not included in the range name.
> > Somewhere, the last line of data is being tracked versus the last line in the
> > range name. Subsequent runs of the DTS appear to be using the last line of
> > data, not the last line in the range.
> >
> > 2) If I manually expand the range name to include the last line of data,
> > then rerun the DTS, the new data is still appended to the bottom of the old
> > data. The old data is cleared leaving blank rows, but the new data is still
> > appended to the bottom; again based on the last line of data. The area
> > covered by the range name returns to being just the headings.
> >
> > Could the fact that Excel is not installed on the machine running the DTS
> > have any bearing?
> >
> > Thanks.
> >
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I am using a DTS created by the Export Wizard to send data to an Excel
> > spreadsheet. The problem is that each time the DTS is run, the data gets
> > appended to the spreadsheet, rather than replaced. I used the Drop and
> > Create Destination Table option on the Transformation window.
> >
> > I found an earlier post regarding this same problem. The suggestion was to
> > use the Delete option in the Transformation window of the wizard. I rebuilt
> > the DTS based on that idea, but get an error message about "Deleting data in
> > a linked table is not supported by this ISAM".
> >
> > Can someone offer some ideas on how to get the data replaced in the Excel
> > spreadsheet?
> >
> > Thanks.
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I cannot rename the spreadsheet because it is tied into other processes. I
> > need to replace the data that already exists in the spreadsheet.
> >
> > If it helps, I did some research since my original posting and here is what
> > I found:
> > 1) When DTS creates the range name in the spreadsheet, it is only the
> > headings of the data. The data itself is not included in the range name.
> > Somewhere, the last line of data is being tracked versus the last line in the
> > range name. Subsequent runs of the DTS appear to be using the last line of
> > data, not the last line in the range.
> >
> > 2) If I manually expand the range name to include the last line of data,
> > then rerun the DTS, the new data is still appended to the bottom of the old
> > data. The old data is cleared leaving blank rows, but the new data is still
> > appended to the bottom; again based on the last line of data. The area
> > covered by the range name returns to being just the headings.
> >
> > Could the fact that Excel is not installed on the machine running the DTS
> > have any bearing?
> >
> > Thanks.
> >
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
DTS Export to Excel
I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
DTS Export to Excel
I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John
DTS Export Object
I have created a DTS object to import a text. This works fine, however, I
need to find a way to 'generate a script' to be able to recreate this same
DTS in another places.
ArthurArthur,
when you Save As ... the package, in the Location dropdown you get 3
choices. You can save to a server, local or remote, as a structured storage
file or as a vb file. I have not worked with a vb file. But if you save as
a structured storage file, you can copy it to other place and there right
click Local Packages and choose Open.
hth
Quentin
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:O7gND6lmDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I have created a DTS object to import a text. This works fine, however, I
> need to find a way to 'generate a script' to be able to recreate this same
> DTS in another places.
> Arthur
>
need to find a way to 'generate a script' to be able to recreate this same
DTS in another places.
ArthurArthur,
when you Save As ... the package, in the Location dropdown you get 3
choices. You can save to a server, local or remote, as a structured storage
file or as a vb file. I have not worked with a vb file. But if you save as
a structured storage file, you can copy it to other place and there right
click Local Packages and choose Open.
hth
Quentin
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:O7gND6lmDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I have created a DTS object to import a text. This works fine, however, I
> need to find a way to 'generate a script' to be able to recreate this same
> DTS in another places.
> Arthur
>
DTS Export fails with "Invalid Object Name"
I have a database that used to have a table called MALL_Customers. The
table was renamed to tbl_Users, and a view was created called MALL_Customers
which simply selects all rows from tbl_Users, for backwards compatibility
with the web app accessing the database.
The database works fine; the web app works fine. All permissions are set
properly on all objects. The view is accessible via EM, as is the table.
However, when I attempt to run a DTS Export of this database to an empty
database on the same server, I get "invalid object name" on MALL_Customers.
Apparently somewhere, in some trigger, stored proc, or some other object,
there is a reference to MALL_Customers that doesn't recognize the VIEW as a
valid object, but is instead looking for a TABLE with that name. I cannot
find it anywhere, and I don't know where to start.
The overall question then is, how do I find all references to the text
"MALL_Customers" in the database schema so that I can track down why my DTS
package is failing. I have another database with a similar problem, and I
think the solution to this problem will also solve the other -- hidden
references somewhere in the database that cause "invalid object name" errors
when using DTS.
Where do I start?
Thanks in Advance,
Marcyou can search through the system table syscomments which, contains entries
for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint,
and stored procedure
Ex:
select * from syscomments where text like '%MALL_Customers%'
Also you can use stored procedure sp_depends to know dependencies of the
existing object.
Ex:
sp_depends 'MALL_Customers'
--
-Vishal
Marc Funaro <marc@.advantex.net> wrote in message
news:xyK7b.3731$nF5.632@.news02.roc.ny...
> I have a database that used to have a table called MALL_Customers. The
> table was renamed to tbl_Users, and a view was created called
MALL_Customers
> which simply selects all rows from tbl_Users, for backwards compatibility
> with the web app accessing the database.
> The database works fine; the web app works fine. All permissions are set
> properly on all objects. The view is accessible via EM, as is the table.
> However, when I attempt to run a DTS Export of this database to an empty
> database on the same server, I get "invalid object name" on
MALL_Customers.
> Apparently somewhere, in some trigger, stored proc, or some other object,
> there is a reference to MALL_Customers that doesn't recognize the VIEW as
a
> valid object, but is instead looking for a TABLE with that name. I cannot
> find it anywhere, and I don't know where to start.
> The overall question then is, how do I find all references to the text
> "MALL_Customers" in the database schema so that I can track down why my
DTS
> package is failing. I have another database with a similar problem, and I
> think the solution to this problem will also solve the other -- hidden
> references somewhere in the database that cause "invalid object name"
errors
> when using DTS.
> Where do I start?
> Thanks in Advance,
> Marc
>
table was renamed to tbl_Users, and a view was created called MALL_Customers
which simply selects all rows from tbl_Users, for backwards compatibility
with the web app accessing the database.
The database works fine; the web app works fine. All permissions are set
properly on all objects. The view is accessible via EM, as is the table.
However, when I attempt to run a DTS Export of this database to an empty
database on the same server, I get "invalid object name" on MALL_Customers.
Apparently somewhere, in some trigger, stored proc, or some other object,
there is a reference to MALL_Customers that doesn't recognize the VIEW as a
valid object, but is instead looking for a TABLE with that name. I cannot
find it anywhere, and I don't know where to start.
The overall question then is, how do I find all references to the text
"MALL_Customers" in the database schema so that I can track down why my DTS
package is failing. I have another database with a similar problem, and I
think the solution to this problem will also solve the other -- hidden
references somewhere in the database that cause "invalid object name" errors
when using DTS.
Where do I start?
Thanks in Advance,
Marcyou can search through the system table syscomments which, contains entries
for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint,
and stored procedure
Ex:
select * from syscomments where text like '%MALL_Customers%'
Also you can use stored procedure sp_depends to know dependencies of the
existing object.
Ex:
sp_depends 'MALL_Customers'
--
-Vishal
Marc Funaro <marc@.advantex.net> wrote in message
news:xyK7b.3731$nF5.632@.news02.roc.ny...
> I have a database that used to have a table called MALL_Customers. The
> table was renamed to tbl_Users, and a view was created called
MALL_Customers
> which simply selects all rows from tbl_Users, for backwards compatibility
> with the web app accessing the database.
> The database works fine; the web app works fine. All permissions are set
> properly on all objects. The view is accessible via EM, as is the table.
> However, when I attempt to run a DTS Export of this database to an empty
> database on the same server, I get "invalid object name" on
MALL_Customers.
> Apparently somewhere, in some trigger, stored proc, or some other object,
> there is a reference to MALL_Customers that doesn't recognize the VIEW as
a
> valid object, but is instead looking for a TABLE with that name. I cannot
> find it anywhere, and I don't know where to start.
> The overall question then is, how do I find all references to the text
> "MALL_Customers" in the database schema so that I can track down why my
DTS
> package is failing. I have another database with a similar problem, and I
> think the solution to this problem will also solve the other -- hidden
> references somewhere in the database that cause "invalid object name"
errors
> when using DTS.
> Where do I start?
> Thanks in Advance,
> Marc
>
DTS execution from client machine fails connecting to Oracle
I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql
DTS execution from client machine fails connecting to Oracle
I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)
DTS execute works, Schedule fails
Hey all,
I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
thanks in advance,
peteI'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...|||when i was running it manually i was sitting at the server so wouldnt it be running in the context of the server?
the scheduled task is run by the /MYSERVER/SYSTEM user... shouldnt that user have all permissions.
how do i change permissions for the user /MYSERVER/SYSTEM if it doesnt have all permissions.
thanks,
peter
Originally posted by Brett Kaiser
I'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...
I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.
Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31
the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")
How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.
thanks in advance,
peteI'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...|||when i was running it manually i was sitting at the server so wouldnt it be running in the context of the server?
the scheduled task is run by the /MYSERVER/SYSTEM user... shouldnt that user have all permissions.
how do i change permissions for the user /MYSERVER/SYSTEM if it doesnt have all permissions.
thanks,
peter
Originally posted by Brett Kaiser
I'd say it's gotta be permissions...
When you run a dts package manually, it runs under the context of the client...
When scheduled it runs under the context of the sql server service account...
Also the same thing with the drive mappings
When you run it, it uses yours...
when scheduled it sees the servers drive
screwed up, huh...
Sunday, March 25, 2012
DTS ERror Email
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
sql
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
sql
DTS ERror Email
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
DTS error [DNETLIB]
I am copying files from one database to another. Identical tables that I
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
Thursday, March 22, 2012
DTS error
Hello, I have been trying to solve this problem for a couple of weeks
now with no progress. I created a package that works when it runs
from my computer but when it runs as a scheduled job, it fails. I
found references to this quite a few times and the responses have
always pointed to permissions, but I am a System Administrator. My
role is Sysadmin. I changed the Windows/NT owner of the job to sa and
that didn't work either. I made sure that the account that runs from
SQL Server has Sysadmin permissions.
now with no progress. I created a package that works when it runs
from my computer but when it runs as a scheduled job, it fails. I
found references to this quite a few times and the responses have
always pointed to permissions, but I am a System Administrator. My
role is Sysadmin. I changed the Windows/NT owner of the job to sa and
that didn't work either. I made sure that the account that runs from
SQL Server has Sysadmin permissions.
The problem I think has to do with the AS400 connection that I make to
pull data out of the AS400 to put into SQL Server 2000. In the
Connection of the package there is a place for User and Password. I
had the person who definitely has all Rumba permissions put his
username and password in there and it came back with the same error.
The Error message is below. I am sure at a loss. Does anyone have
any ideas? I would really appreciate anything. Could this have
anything to do with MDAC?
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
failure. APPC Primary RC = Parameter_Check; Secondary RC =
Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
link failure. APPC Primary RC = Parameter_Check; Secondary RC =
Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
Process Exit Code 1. The step failed.phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> Hello, I have been trying to solve this problem for a couple of weeks
> now with no progress. I created a package that works when it runs
> from my computer but when it runs as a scheduled job, it fails. I
> found references to this quite a few times and the responses have
> always pointed to permissions, but I am a System Administrator. My
> role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> that didn't work either. I made sure that the account that runs from
> SQL Server has Sysadmin permissions.
> The problem I think has to do with the AS400 connection that I make to
> pull data out of the AS400 to put into SQL Server 2000. In the
> Connection of the package there is a place for User and Password. I
> had the person who definitely has all Rumba permissions put his
> username and password in there and it came back with the same error.
> The Error message is below. I am sure at a loss. Does anyone have
> any ideas? I would really appreciate anything. Could this have
> anything to do with MDAC?
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.
As a complete guess, does the Rumba driver use information stored in
your profile? I recall that the IBM Client Access driver used to do
this - AS/400 connections were defined and stored in each user's
profile, so when a task was scheduled, it failed, because the profile
wasn't available.
Simon|||What's your DSN setup look like (for iSeries connection)?
phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> Hello, I have been trying to solve this problem for a couple of weeks
> now with no progress. I created a package that works when it runs
> from my computer but when it runs as a scheduled job, it fails. I
> found references to this quite a few times and the responses have
> always pointed to permissions, but I am a System Administrator. My
> role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> that didn't work either. I made sure that the account that runs from
> SQL Server has Sysadmin permissions.
> The problem I think has to do with the AS400 connection that I make to
> pull data out of the AS400 to put into SQL Server 2000. In the
> Connection of the package there is a place for User and Password. I
> had the person who definitely has all Rumba permissions put his
> username and password in there and it came back with the same error.
> The Error message is below. I am sure at a loss. Does anyone have
> any ideas? I would really appreciate anything. Could this have
> anything to do with MDAC?
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.|||We are using a NetManage ODBC for AS400/Rumba with TCP/IP enabled. We
have it look directly at the library where we are trying to get the
data from. The connection has been made on the SQL Server box in
control panel. Is that what you mean?
I have literally tried everything I can think of. I am very
frustrated.
arrowcomputer@.bigfoot.com (Terence) wrote in message news:<440c28e5.0404070344.663f9d39@.posting.google.com>...
> What's your DSN setup look like (for iSeries connection)?
>
> phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404061235.5e1e3334@.posting.google.com>...
> > Hello, I have been trying to solve this problem for a couple of weeks
> > now with no progress. I created a package that works when it runs
> > from my computer but when it runs as a scheduled job, it fails. I
> > found references to this quite a few times and the responses have
> > always pointed to permissions, but I am a System Administrator. My
> > role is Sysadmin. I changed the Windows/NT owner of the job to sa and
> > that didn't work either. I made sure that the account that runs from
> > SQL Server has Sysadmin permissions.
> > The problem I think has to do with the AS400 connection that I make to
> > pull data out of the AS400 to put into SQL Server 2000. In the
> > Connection of the package there is a place for User and Password. I
> > had the person who definitely has all Rumba permissions put his
> > username and password in there and it came back with the same error.
> > The Error message is below. I am sure at a loss. Does anyone have
> > any ideas? I would really appreciate anything. Could this have
> > anything to do with MDAC?
> > DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> > DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> > DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> > DTSStep_DTSDataPumpTask_1 DTSRun OnError:
> > DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
> > string: [Wall Data][RUMBA Data Access][RUMBA95]Communication link
> > failure. APPC Primary RC = Parameter_Check; Secondary RC =
> > Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> > Drivers Help file: Help context: 0 Error Detail
> > Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
> > Error string: [Wall Data][RUMBA Data Access][RUMBA95]Communication
> > link failure. APPC Primary RC = Parameter_Check; Secondary RC =
> > Bad_LU_Alias. Error source: Microsoft OLE DB Provider for ODBC
> > Drivers Help file: Help context: 0 DTSRun OnFinish:
> > DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete.
> > Process Exit Code 1. The step failed.|||I found a reference to this when searching. It may be of help to you.
http://support.microsoft.com/?kbid=269074
I have a similar problem in that my DTS package runs for me (with sa
privileges) but I can't get it to run as a scheduled task. I've not
found the solution yet, but if I do, I'll post something here in case
it is of use.
Ryan|||Thanks. I had read through that article a few times. I appreciate
your help and would love to hear your solution if you find one.
Today, I stopped all the services, changed the sql server account to
System and rebooted hoping that would solve my problem. It did solve
another problem I was having with a catalog failing to populate, but
it did not fix my package problem.
Something I left out is that my scheduled backups run just fine. So I
am wondering, if the problem is with SQL Agent permissions...wouldn't
the scheduled backups not run either? If that is the case, then is it
possible that the connection I am making to the AS400 is somehow not
getting the Username and password I specified in the connection
properties? If that could be the problem, does anybody have any
suggestions on how I might test that or fix it? I can't think of
anything else to try. I am out of ideas.
ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404080146.518ebe9d@.posting.google.com>...
> I found a reference to this when searching. It may be of help to you.
> http://support.microsoft.com/?kbid=269074
> I have a similar problem in that my DTS package runs for me (with sa
> privileges) but I can't get it to run as a scheduled task. I've not
> found the solution yet, but if I do, I'll post something here in case
> it is of use.
> Ryan|||Well, I finally got it to work today. After reading everything I
could get my hands on I put the right peices together. I had to
first, give the domain user, in our case, domain\Administrator
sysadmin rights in SQL Server. I figured that one out a while ago.
Then I had to go to the Domain Security, User Rights Assigns in Active
Directory. I modified the administrator user rights assignments to
allow it to Act as part of the operating System, Increase Quotas,
Replace a proces level token, Log on as a batch job, and Log on as a
service. To make sure everything was in sync, I stopped all services,
made sure SQL Server's account was that of the
domain\Administrator...made sure that the SQL Server Agent had the
same account, rebooted SQL Server, which restarted all the services,
and voila...my scheduled jobs will now run. Thanks to all who tried
to help. I knew it had to do with permissions, but darned if I could
figure out what to do about it. Maybe this will help someone else.
phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404081147.67ec4d5d@.posting.google.com>...
> Thanks. I had read through that article a few times. I appreciate
> your help and would love to hear your solution if you find one.
> Today, I stopped all the services, changed the sql server account to
> System and rebooted hoping that would solve my problem. It did solve
> another problem I was having with a catalog failing to populate, but
> it did not fix my package problem.
> Something I left out is that my scheduled backups run just fine. So I
> am wondering, if the problem is with SQL Agent permissions...wouldn't
> the scheduled backups not run either? If that is the case, then is it
> possible that the connection I am making to the AS400 is somehow not
> getting the Username and password I specified in the connection
> properties? If that could be the problem, does anybody have any
> suggestions on how I might test that or fix it? I can't think of
> anything else to try. I am out of ideas.
> ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404080146.518ebe9d@.posting.google.com>...
> > I found a reference to this when searching. It may be of help to you.
> > http://support.microsoft.com/?kbid=269074
> > I have a similar problem in that my DTS package runs for me (with sa
> > privileges) but I can't get it to run as a scheduled task. I've not
> > found the solution yet, but if I do, I'll post something here in case
> > it is of use.
> > Ryansql
DTS Email Task Works - But not in JOB
I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thanks
taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thanks
taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
DTS Email Task Works - But not in JOB
I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
DTS Email Task Works - But not in JOB
I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm
Wednesday, March 21, 2012
DTS copy table from source DB->destination DB
I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination
I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.
I just want to transfer the data and schedule it but when I ran the package it said:
'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'
I re-opened the 'Data Driven Query Task'
I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:
INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()
But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.
Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?
Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.
So, when I run it now I get the error
'The data driven query task requires at least one query (and associated columns) to be specified'
I don't want to set any queries. I just want the transformation to run when i execute the package.
Any ideas?|||Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>
Thanks,
I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.
i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.
I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.
I just want to transfer the data and schedule it but when I ran the package it said:
'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'
I re-opened the 'Data Driven Query Task'
I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:
INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()
But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.
Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?
Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.
So, when I run it now I get the error
'The data driven query task requires at least one query (and associated columns) to be specified'
I don't want to set any queries. I just want the transformation to run when i execute the package.
Any ideas?|||Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>
Thanks,
I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.
i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.
DTS Copy SQL Server Task
I want to copy a database on a remote server to a local instance of SQL Server. I created a package that has the Copy SQL Server Task in it, but it seems to fail half way through every time i execute it.
I can view the local machine error log which simply reports that the table transfer was attempted.. but no explanation of why it fails. I don't have access to the host error log files either, so I have no idea why it fails.
I once almost got the 'Copy SQL Server Task' to work, but it would create duplicate records in the destination database, which I didn't want. It seems choosing the intuitive options like 'replace existing data' and choosing the table I want copied over.. does not work. I am not 100% sure the 2 databases are referentially identical either.. so it would be nice to know how to do a copy by first deleting the entire database on the destination machine, then recreating it all.
Can anyone give me some advice on which options I need to choose to achieve a proper database copy? Or, if you could show me a tutorial online that could explain how all the options work, and how I can debug what's causing my current method to fail, would be nice.
BrentWell I got it to work. It turns out that you can double click the error message to get some more details as it occurs. Strangely every account I had tried to use on my local server did not have permission to do the copying.. but only for certain attributes. So, basically by trial and error, I selected the right configuration to only copy the tables to the destination database (the accounts seemed to have permission to do certain activities).
It sure would be nice to find some info on the meaning of all the options in help or online somewhere.. if anyone knows of such a resource, they should post it.|||Thanks me!
I forgot how to view DTS backup log errors, and found my own old post to show me... double click the error in Ent. Manager!
This is what i needed to fix the problem!
Subscribe to:
Posts (Atom)