Moving A File Using Activex In DTS

Jun 17, 2004

I am attempting to export a records to an excel file using DTS and then use activex to rename the file to include the date it was run. To do this i just move the file.

However when the script is run, it fails saying that permission is denied on the "move" line. I have seen other reports saying that permission is denied when using the "CreateObject" however i have implemented the changes it says, ie/ adding the SQL Agent login using the DCOMcnfg utility and renaming the DefaultAccessPermission key in the registry, with no success. It also doesnt seem to fail on that line anyway.

I have also added all the groups and usernames with full control to the security of the folder the file is in to no avail either.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

Set fso = CreateObject("Scripting.FileSystemObject")

'
Set bFile = fso.GetFile("c:DTS reportsoutput.XLS")
aYear = year(now)
aMonth = month(now)
aDay = day(now)
filename = "output" &aYear & aMonth & aDay & ".xls"
bFile.move ("c:DTS Reports"&filename)
'fso.MoveFile "c:DTS Reportsoutput.xls", "c:DTS Reports"&filename

Set fso = Nothing

End Function

Does anyone know what kind of permissions the folder the file is in must have in order for this script to work? Any help would be greatly appreciated.

Thanks

View 2 Replies


ADVERTISEMENT

DTS ActiveX BCP File Manipulation

Apr 29, 2007

Does anyone know the best way to handle this type of situation?

A file coming into a directory based on the date filename042707
I use the fileexist stored procedure to check for the existence.
I use xp_cmdshell(sp)... stored procedure to rename the file so that
it just has filename instead of the date.... I can not use
variables within the xp_cmdshell to replace the date...
everyday the filename would change to filename042807,filename042907 etc....

Basically I know how to copy the file to another directory if it exists...
then I want to strip the right 8 characters off... rename just to that
but with the rename i have to know what the file will be named for that day ...
excuse the grammar just somewhat tired...
any suggestions... please thanks time for sleep

View 2 Replies View Related

ActiveX To Copy And Rename File

Feb 3, 2006

Hi,

I'm looking to use ActiveX in a DTS to copy a file from on elocation to another.
I have the code for this (attached below kindly supplied by a colleague), so that's cool.

dim filesys
set filesys=CreateObject("Scripting.FileSystemObject")

If filesys.FileExists("D:CreditsCredits_MTD.mdb") Then
filesys.CopyFile "D:CreditsCredits_MTD.mdb", _
"C:CreditsCredits_200602.mdb"
End If

But what I'm looking to do is go one step further.
As you can see, it renames the file to yyyymm, but this is hard coded, and I'm looking for a coded solution so I can rename the file with yesterday's date.

Can anybody help me please?

View 4 Replies View Related

Selecting A File In DTS Using ActiveX Script

Jan 5, 2004

Hi Everyone,

I have a question regarding transforming a text file from a folder into a table using DTS. The text file is on a different server and the name is in the format --- Orders040105.txt I am able to select the file using 'FileSystemObject' as the file required changes everyday, but I do not know how to use the file for the transformation using ActiveX Scripting. I have 2 'Activex Script Tasks' in the DTS Package--- one for selecting the text file and the other for the Transformation.

Function Main()
??????????????????????

DTSDestination("Order_Date") = DTSSource("Order Date")
.
.
.
End Function


Thanks in Advance

View 3 Replies View Related

Using The Flat File OLE DB Provider In An ActiveX Component

Jun 23, 1999

Does anyone have any examples of using VB to automate a DTSPumpTask to import and transform a flat file. How do you use the dtsffile.dll OLE DB provider for an ADO connection or command object? I would like to create a task to transform a flat file and be able to pass in various parameters to make the load flexible. If possible respond to ScottMiles@Epotec.com

Thanks for any help.

View 1 Replies View Related

Close An MS Access File With An ActiveX Script??

Apr 7, 2005

Hi,

I have an ActiveX script running in a DTS package that checks for the existence on an Access.mdb file (using the filesystemobject), deletes the file if it's there and then re-creates a fresh empty Access database (using ADOX). This is great except one problem. If someone has the original Access mdb file open while I'm running this script, it's impossible to delete the file while its 'in use'. I'm not sure how to get around this problem. I've tried researching the filesystemobject to see if I can close the existing file before deleting it but the only Close method I found applies to text stream documents created with fso.

Does anyone have any ideas about how solve this problem?

View 3 Replies View Related

MSSQL - DTS Package - Find Distinct Rows - Output To TXT File - ActiveX?

Jun 11, 2007

Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)

View 7 Replies View Related

File System Task-Moving File

Jan 19, 2008

I have the following directories

\servershare
\serversharearchvie

I have a for each file loop and inside it a data flow that pulls from one of the flat files in the directory and then a file system task. If I choose the "Move File" option in the file system task to move the file to the archive directory, it fails with an access denied message. The access denied message occurs after a message saying file was successfully deleted. I am running this from BIDS right now and my local user can write delete etc in both the above directories. However, if I do a "copy file" in the file system task it seems to work. I think what is happening is it is deleting the file first and then trying to move it, but it no longer exists because it has been deleted--is this possible? Is this a bug of some sort?

For now I am going to workaround by putting in another file system task that deletes the files after they were copied and see how that goes, but would prefer just to do the "move" option.

Thanks,
Kayda

View 3 Replies View Related

File System Task - Moving A File

Nov 7, 2006

I have a File System Task that uses variables to resolve the destination and source paths of a document. When I select the 'copyfile' operation...the document is copied from the source to the destination without error.

However when I change the property from 'copyfile' to 'movefile' I get an error and the document is not moved.

The source and destination variables contain a valid file path name since the copy commmand is working as expected. However when I alter the properties of the File System task to move the document. I get the following error:

Could not find a part of the path 'G:CommonInformation SystemsDropFilesrtNRT_ConfirmationOrder Confirmation Report_11062006.xlsOrder Confirmation Report_11062006.xls

It seems a little nonsensical since the document file paths are valid when performing a copy. For some reason the error log is showing that that the file path is the document name "Order Confirmation Report_11062006.xls" and adding it twice to the the directory path called "'G:CommonInformation SystemsDropFilesrtNRT_Confirmation" as you can see in the above error message.

To replicate the 'move' action...I added an extra File System Task that deletes the document once the copy has been performed. I would like some insight into why this doesn't seem to work.

Thank you.

...cordell...

View 1 Replies View Related

[ActiveX Script Task] Error: Retrieving The File Name For A Component Failed With Error Code 0x001B6438.

Jul 28, 2006

This error occurs when the ActiveX task tries to execute:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.







View 8 Replies View Related

MOVING DATA AND LOG FILE

May 25, 2001

I am trying to move a data and log backup file from my C drive to another drive to free my C drive. Does anybody know the easiest way to do it? Thanks.

View 6 Replies View Related

Moving SQL7 Log File

Oct 25, 1999

To all,

How can I move the log file from drive C: to drive D:. I do not need to keep the log file contents and can start new.

Thanks,

Frank

View 2 Replies View Related

Moving A Log File Location

May 14, 2008

I've got some users that created a database with the log file on a drive that doesn't have a lot of space. I'd like to truncate the log and move it to a different drive. I can truncate it, but is moving it as easy as changing the files properties through SSMS?

Thanks

View 2 Replies View Related

Moving The ASPNETDB.MDF File To Another Computer

Jul 22, 2006

Hi all!I have some problems with asp.net windows authentication.Asp made the file ASPNETDB.MDF. But I dont have SQL Server on the machine where the application will be deployed...So I have to move this file to another server, but I couldnt find any information about ASPNETDB.MDF connection string or somenthing like this.Is it possible to move this file or I have to make the authentication on my own, with my own database? I hope there is a solution to this problem!Many thanks! Regards!

View 1 Replies View Related

Moving Transaction Log File To Different Drive

Jan 19, 2001

Hi,

I'm trying to move the transaction logs of my databases to a different drive (for fault tolerance). I can create a second transaction log file for each database via Enterprise Manager but I have 2 questions:

1) If two transaction log files exist for a database which one does it use ?

2) How do I force SQL to use the new transaction log file ? (so I can delete old)

Thanks,

Tim

View 4 Replies View Related

Moving Transaction Log File In SQL Server V7

Mar 2, 1999

I'm in the process of tuning a SQL Server v7 box and have decided that the transaction log would be better placed on its own disk. How do I move a transaction log ?

View 1 Replies View Related

Moving Transaction Log File To Another Disk

Apr 5, 2004

Hi all,
I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).
Now I have to shift the log files to a seperate disk.
I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?
Anycomments welcome.
Thankyou.
regards,
harshal.

View 1 Replies View Related

Index Moving To A New File Group

May 7, 2008

I have a requirement for moving all the non-clustered indexes to a new file group. Does anyone have a script for this?

Thanks.

------------------------
I think, therefore I am - Rene Descartes

View 1 Replies View Related

Moving The Data File To Another Location

May 17, 2007

Hi, I am working on a new installation which I did not set up and realized was using the wrong partition of the server to store the data and log files, I have already created several databases, I want to use another partition for these databases without having to drop them and create them all over again.
In BOLine i saw this command but want to make sure its safe, hope somebody can comment on this or if I am missing something.thank you

proposed command:
"MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )"

View 7 Replies View Related

Moving Table To New File Group

Oct 17, 2007

Hi all

I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following

1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)

2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO

3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]

The above command executed successfully and now the table is in new file group [FG_ACTV]

My question is how come the table was moved to new file group though it has no files attached to it?

Thanks in advance.

View 5 Replies View Related

File Task Moving With Variables...

Apr 25, 2007

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.



Source Path & Filename:

@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]

Outputs:

\umrdwh2FTPCLAIMDAILY.DATA



Destination Path Now: (Filename not specified per another thread.)

@[User::WorkingFolder]



Original Destination Path: (Which did not work.)

@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]



I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0





How do I overcome this without hard coding the path in the task?












View 11 Replies View Related

Moving A Text File To Sql Server

Feb 17, 2008

I have a text file and moved the data to sql server using import and export data. These are step I am doing.

1. Open Microsoft Sql Server and select DTS Import Export Wizard
2. Using the wizard , I am selecting the Data Source as Text File and provided the file name.
3. In the next option, I am choosing Delimited , followed by Tab option.

4. Then, I am selecting the required Destination and the required Database also.
5. I am choosing the files specified and Run the file.

This method works perfectly.

However, I want a method, where I could avoid steps 1 thru 5 and provide the user a singe step. I mean just an execute of a stored procedure should do the steps 1 thru 5. Is that possible.

Thanks.

View 8 Replies View Related

SQL Conn. Problem After Moving .mdf File

Aug 11, 2007

Hello ,


I am using SQLSExpress 2005 and VS2005.

Yesterday, i changed my sql connection string and i added ;

AttachDbFilename=|DataDirectory|\dbase.mdf

And i moved mdf file to project's debug directory by using cut-paste. I deleted dbase from databases section in SQL Managament Studio. And i attached dbase according to new path(project debug path).

After these, problem started to occur. If i open a table in SQLMS i cant start debugging my application, it throws
exception " Cannot open user default database .login failed. "

And when i start my application , i cant use SQLMS to see my tables or to run query.It says ; ...cannot be opened due to inaccesible file....

It seems like only 1 application can use my db at a time. But before i moved my database, i could both open tables in SQLMS and i could start running my application at the same time


Do u have any idea?
thankss

View 6 Replies View Related

Moving Db Via Detach Then Copy And Paste 44 Gig Db File?

Aug 21, 2000

Hi,

I have a 44 gig db that I moved to another server via the NT copy and paste method. The db has gotten a little larger, and now NT gets almost to the end, but can not complete -- either starts over completely and just does the last few minutes over and over.

Is there an NT copy and paste limit? Any ideas what the problem is? Is the db mixed up?

Thanks for any help.

Judith

View 4 Replies View Related

Moving Table To A New File Group (SQL2K5)

May 19, 2008

How to move a table to a new file group when the table DOES NOT HAVE A CLUSTERED INDEX.

I also don't want to , create a new table, move the data, drop the old table.

Is there a easier way?


------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

File Groups: Moving A Large Table

May 8, 2007

We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?

The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.

Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?

Like I said a rough estimate is fine.

Many thanks,

Scott

View 4 Replies View Related

Setup And Upgrade :: Moving Log File To Different Disk

Sep 16, 2015

Do you have to move the entire content of the log folder to the new location?

View 4 Replies View Related

Sql Server Express Problem When Moving From VWD File Based To IIS

Dec 5, 2006

Hi All,
 
We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test.
 
However, when we get to a page that tries to access our Sql Sever Express database, we get the following error:
An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Does anyone know how to overcome this problem? Any help appreciated.
Claude.

View 1 Replies View Related

SQL Server 2008 :: Moving Tables To New File Group

Mar 16, 2015

I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.

So, how do I get the free space out of the data file? - I've decided to:

1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables

There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?

View 8 Replies View Related

SQL 2012 :: Free Space After Moving Tables From One File Group To Another

Aug 13, 2014

Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?

View 2 Replies View Related

Integration Services :: Moving File From One Folder To Another Using C# Script Task

Sep 11, 2015

I have 2 source folder .I have created variable for both source folder like below

User::Source1 and User::Source2
and 1 destination folder variable like below
User::Destination

Now I have to search a file from both source folder which consist of *location_*.xml string in file name.I have to use C# Script task ,achieve above scenario as I required it on very urgent basis.

View 5 Replies View Related

Recovery :: Moving DB Backups From File Share Witness AlwaysOn

May 31, 2015

Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...

View 5 Replies View Related

Integration Services :: SSIS Package Cannot Perform Moving File Operation

Oct 25, 2015

I have an SSIS package, that move file from one folder (Download) to another folder (Working), where it will be processed and passed to (Processed) folder. The folder (Working) is created at run time and deleted after finishing process. I ran this package using SQL Server Agent (I created a sql job). My problem is that the package fails to move the file from Download to Working, Although it can move it to other folders (say I skipped Working and move it directly to the already-created folder "Processed").

I traced the problem and found the error "Access is denied", when run the package without Agent (double click). I provided the necessary permissions to all levels of folders to the user XX, which I made it the (SQL Server Agent Service Account) as well as the Job Owner. By this, the package executes successfully (again by double clicking it), but with Agent it FAILS.

Why Agent cannot move the file to the run-time-created folder (Working) ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved