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


ADVERTISEMENT

File Manipulation From MS SQL Server

Oct 28, 2004

I am working on an application which uses DTS to move data into temporary tables. I would like to be able to rename/relocate the source file in order to maintain a historical reference. The process which creates the source file is not flexible at all. Is there a way to manipulate the file's name and/or relocate the file by using SQL Server.

Thanks in advance!

Daniel
Austin, Texas

View 4 Replies View Related

SQL Server 2005 - FILE Manipulation Hanging....

Aug 21, 2006

Hi,I recently installed SQL Server 2005 Enterprise Edition (9.0.1399) andI have problems on queries concerning system file manipulation...For example, when I try to increase the Log File of MSDB database :ALTER DATABASE msdbMODIFY FILE(NAME = 'MSDBLog',SIZE = 50MB)This query never terminates...All SQL queries for database creation never terminateCREATE DATABASE EASYSHAREON PRIMARY(NAME = EASYSHAREData,FILENAME = 'D:AQSACOMDATAEasyshareEasyshareData.mdf',SIZE = 200MB,MAXSIZE = 1000MB,FILEGROWTH = 100MB)LOG ON(NAME = EASYSHARELog,FILENAME = 'E:AQSALOGEasyshareEasyshareLog.ldf',SIZE = 100MB,MAXSIZE = 300MB,FILEGROWTH = 50MB)Would someone have an idea of what happens...I'm running on Windows 2000 Server...ThanksPatrick

View 2 Replies View Related

String Manipulation From Flat File Into Database

Jan 9, 2008



Hi

I'm used to DTS but new to SSIS. What's a good reference/tutorial that deals with transforming columns of data (from a flat file) from one format to another when uploading into SQL2005? Typically columns of data have "" around the values and spaces that I want to remove.

Presumably in SSIS I need the following:

A Data flow task containing:

Flat file source
Derived or Copy Column?
OLE DB Destination

Is this on the right track?

Thanks
Gerry

View 1 Replies View Related

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 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

Excel File Manipulation - Repeat First 7 Columns Based On Number Of Lines In Transaction

Jul 10, 2015

I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.

I am attaching a sample spreadsheet with two tabs - current structure and desired structure.

Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.

The number of lines is variable per patient.

View 6 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

Exported Flat File Data Will Not Import To Same Table Without Extensive Data-type Manipulation

Jul 13, 2007

I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."



Seems simple, right?



I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?



Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.



View 12 Replies View Related

Backup Failed: System.Data.SqlClient.SqlError: Backup And File Manipulation... Must Be Serialized

Jul 11, 2007

Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:

Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)



Program location:

at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)



Backup Options were set to:

Back up to the existing media set

Overwrite all existing backup sets



I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?

View 11 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

Row Manipulation

May 29, 2008

col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1

Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1

and the last set
14LKJ2
15IJ1

Any help is appreciated!!

View 1 Replies View Related

Dataset Manipulation

Mar 1, 2007

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.
I loop through that data to load a check list which also works wonderfully.
 two questions based on that.
1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.
2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)
 
sorry for the fairly basic question. i appreciate the help.
 Justin
 

View 2 Replies View Related

String Manipulation

Apr 2, 2001

I am totaly confused please help

I am trying to change a tring of 7 characters of the format "XXYZZZZ" to be
"XX0YZZZZ" I wonder if any body has any idea

Also how can get an out of a dattime field in the format of DDMMYYYY and converted into text.

You help is highly appreciated

View 1 Replies View Related

STRING MANIPULATION

May 15, 2001

Hi!

I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:

CANADA - Toronto
United States- ARIZONA
France
Argentina
United States (USA)- ARIZONA
........
........

The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:

Server: Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

QUERY
-----
select

Left(
Destinations.[Destination Name],
charindex("-", Destinations.[Destination Name])-1
)
as test
into temp2
from destinations

CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).

View 1 Replies View Related

Record Manipulation

Feb 3, 2003

Hi,

I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?

Please help!!

Thanks!!

View 1 Replies View Related

String Manipulation ?

Nov 30, 2004

I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname

crab,mike,Allen
Lota Weilly,Eric,M

My script to do this looks like

update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))

My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names I get the following error

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

It seems to be related to the first name, I can comment out that update and it works

Thanks
Thanks

View 11 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 2 Replies View Related

String Manipulation

Nov 13, 2005

Field1 = Dominguez Public Transport Division 03 9320 4326

how do i remove these strings in T-SQL

Field1 = Dominguez
Field2 = Public Transport Division
Field3 = 03 9320 4326

View 3 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 3 Replies View Related

Text Manipulation

Apr 30, 2008

Hi, is there a way i can replace multiple white spaces & tabs into one , on a TEXT column ?

I am using replace function and trying to store the result into a varchar variable and the TEXT being truncated after 255 charecters.

for example, I tried this :

declare @str varchar(4000)
select @str = replace(cast(textcolumn as varchar(4000)), ' ', NULL)

I understand t-SQL dml statement(s)
will automatically truncate after 255 chars in a long string. But, seeking for other options here pls.

thanks for ur help..

View 6 Replies View Related

String Manipulation

May 3, 2008

Sir

How i m Manipulate String , I have
(ABC,XYZ,EFG) this string
know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get

like (ABCWE,XYZRT,EF)
pls help me out

Yaman

View 2 Replies View Related

String Manipulation

May 29, 2008

Hi
I have a field which is a file path
like
'C:avde8393948.txt'

I want to separate them into folder and filename
now the filename is always the same length,
so i can use RIGHT to get the filename,
but i prefer a method that from the right detects the
1st occurance of and everything after is the filename,

View 2 Replies View Related

Using Cursor For Manipulation

Oct 17, 2014

ALTER procedure [dbo].[sp_debit_credit]

as begin
SET NOCOUNT ON
DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)

[Code] ....

table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00

[Code] ....

I need result, I have to subtract

(DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1

Using with cursor. It will check row by row , i have written stored procedure , but flag is not updated .

View 1 Replies View Related

Date Manipulation

Jul 20, 2007

I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).

I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...

Thanks in advance.

TableA

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0


TableB

MemberId EmployerId EffectiveDate
12345 444 4/1/2006


TargetTable (TableA Updated)

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0



Thanks
Suresh

View 4 Replies View Related

Date Manipulation

Jul 23, 2007

I have a table with one column as a date field in the form of mm/dd/yyyy and I would like to create a new column on a report as 'Days Open' using the column with random dates in the past and subtracting it from the current system date. Can anyone provide any assistance. I'm very new to SQL Server. I know in Oracle u can use 'sysdate'

View 2 Replies View Related

String Manipulation

Aug 29, 2007

Hi If i have a string like :

AX4030303022

in one field and need to split it into

AX 030303022

I can use LEFT(field_name,2) for the 1st one, but
what can i use for the 2nd ?
and how can i deal with NULLS ?
if its NULL i want a blank space..

View 16 Replies View Related

Data Manipulation

Dec 26, 2007

Hello,

I have to perform some data manipulation based on ID1, ID2 with respect to Effective dates (below represented as effdt)
Source Code:

create table #source (id1 int, id2 int, effdt datetime)
insert into #source values (111, 123, '1/1/2007')
insert into #source values (111, 123, '3/1/2007')
insert into #source values (111, 123, '4/1/2007')
insert into #source values (111, 124, '5/1/2007')
insert into #source values (111, 125, '6/1/2007')
insert into #source values (111, 123, '7/1/2007')

Source Code Data:

ID1 ID2 Effdt
111 123 1/1/2007
111 123 3/1/2007
111 123 4/1/2007
111 124 5/1/2007
111 125 6/1/2007
111 123 7/1/2007

Target Data should look like:

ID1 ID2 Effdt Expdt
111 123 1/1/2007 4/30/2007
111 124 5/1/2007 5/31/2007
111 125 6/1/2007 6/30/2007
111 123 7/1/2007 7/31/2007


The logic is like the min(effdt) as effdt, min(effdt) - 1 as expdt of the next id2 from the source. Nut the problem comes when the same ID2 comes later after a different id2, so just a group by on id1, id2 does not suffice.
If it is the last record then the expdt is that month's end date.
Can someone throw me some light on what kind of logic I should be using to accomplish this.

Thanks in advance.
Greg

View 2 Replies View Related

Binary Value Manipulation

May 1, 2007

I would like to drop the leading 0x on a binary value so I can do abitwise operation.Here is simplified code:select right(0x88186000,8)I expected to get back 88186000, this was not the case. The commandreturned some wierd characters.Am I missing something?

View 3 Replies View Related

String Manipulation SQL

Jul 20, 2005

We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid

View 3 Replies View Related







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