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
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.
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
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
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.
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.
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
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.
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
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?
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 :-)
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.
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?
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
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
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).
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?
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
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.
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.
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
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,
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 .
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...
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'
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.
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?
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
