Modify The File Dir In A DTS Com File

Sep 14, 2000

Hi,

i made the stored proc :

ALTER PROCEDURE ExecuteDTSPackage @ServerName VarChar(15),@UserID VarChar(15),@Password varChar(15),@PackageName VarChar(50)
AS
Declare @obj int
Declare @conn int
Declare @hrobj int , @hrconn int
Declare @return varChar(255)
Declare @CMD varChar(255)
Declare @prop int
Exec @hrobj=sp_OACreate 'DTS.Package', @obj OUT
if @hrobj=0
begin
set @CMD='LoadFromStorageFile("D:logmetrixDTS_Packages DTS_Domainlev1.DTS",Null)'
EXEC @hrobj = sp_OAMethod @obj, @CMD
if @hrobj=0
begin
--SELECT FILEPROPERTY(@obj, 'Connections(0)')
--Exec @hrconn=sp_OACreate 'DTS.Package.Connections(0)', @obj OUT
--EXEC @hrconn = sp_OASetProperty @obj, 'Connections(0).Datasource', 'd:logmetrixcountrydomainlev1.csv'
EXEC @prop = sp_OAGetProperty @obj, 'CreationDate'
EXEC @prop = sp_OAGetProperty @obj, 'AutoCommitTransaction'
--print @hrconn
--print @dtcreate
IF @hrconn = 0
BEGIN
Print 'Ok'
RETURN
END
end
else print 'Error Loading Package.'
Exec sp_OADestroy @obj
end
else print 'Error loading OLE Package.'
go

I can acceed to the properties of my object DTS.package @obj but I don't know how to acceed to the properties of my first connection (I made a import with DTS Wizard from a flat data file to a database table (SQL Server). I need to change the filedir dynamically. It would be in the datasource or datafile property of this connection.

How can I change this fiel directory ???

Thanks to your answer

Axel

View 1 Replies


ADVERTISEMENT

Modify Physical File Name

Oct 27, 2003

I know how to modify the logical file name: ALTER DATABASE SATutorial
MODIFY FILE (NAME = Tutorial, NEWNAME = SATutorial_data)
GO

How would I modify the physical file name from (e.g.) Tutorial.mdf to SATutorial_data.mdf?

TIA...

Al

View 3 Replies View Related

Is It Possible To Modify A Report By Using .rdl File?

Nov 7, 2007

Hello,

I need to modify an existing report in SSRS 2005, but I can get its ~.rdl file, no solution. Can I use ~.rdl file to repair the report?

Thank you!

View 5 Replies View Related

SQL XML :: Iterate And Modify XML File

Sep 15, 2015

I've a problem with this XML file shown below.

<Prestazione>
  <DatiPdR>
    <cod_pdr>1111111</cod_pdr>
    <Lettura>
      <let_tot_prel>3522</let_tot_prel>

[Code] ...

In particular i want to replace the value of all <let_tot_prel> tags, but i can't manage to do what i want. 

View 5 Replies View Related

Get A File&#39;s Create/Modify Date

Jun 17, 1999

HOW TO GET A FILE'S CREATE DATE??
From SqlServer I need the create date of a file on C or D.
Is there a function or code to retrieve the create date,
or an API that could read and pass back the file's creation date???

View 1 Replies View Related

Modify File On User Database

Jan 20, 2008

Hello, I have gone to work for a new company and I have seen this company is running out of drive space on a daily basis. The DBA before me created huge databases. For example we have a database that it's created size is 103 GB. Only about 52 GB is ever needed. I know you can resize the tempdb but how about user databases? I have never tried this. Can this be done for a user database?

For tempdb
Start SQL from cmd line sqlserver -c -f -s%instance_name%
alter databse tempdb modify file

(name='tempdev',size=150)

View 3 Replies View Related

SQL Server 2008 :: ALTER Database To Modify Log File

Jun 25, 2015

We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1

Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The statement has been terminated."

Now tried to change the filegrowth of the log file to unlimited

ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);

The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB

View 6 Replies View Related

SQL Server 2014 :: Alter Database Modify Log File

Nov 6, 2015

I can run this command to make changes for 1 DB USE [master]

GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.

View 2 Replies View Related

Create/modify File With A System Stored Procedire

Nov 22, 2005

Does anybody know how to create/modify a file without using one of the system stored procedures?

Thanks

View 1 Replies View Related

Modify The RSReportServer.config File With Web Service Call

Jan 11, 2008

Is there a way to modify the RSReportServer.config file with a Web Service call. We want to install an app and have it modify the Renders. We want to remove the Tiff export for example. We also want to add simple headers to Excel. Right now we have to open the file and then make our edits.

View 1 Replies View Related

Sql 2005 Install Error. MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Jun 15, 2006

I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message



native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.


I'll try to post the full log in a new post.



View 11 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. Im using?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesnt keep the changes.



Any help with this process?

View 1 Replies View Related

Reporting Services :: Parsing SSRS Config File And Dynamically Changing File Path Of Config File In Code

Sep 2, 2015

Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url.  My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?

Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc. 

I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).

Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.

View 2 Replies View Related

File Will Not Attach To SQL Reason File Is Not Primary File

May 10, 2007

I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?

mdf file size 5,738,944 KB
ldf file size 10,176 KB

View 4 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

Attach Mdf File And Data File Without Log File

Mar 1, 2006

how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file

View 1 Replies View Related

Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?

Jun 18, 2015

I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.

I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.

1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?

2. Does Indexs Reorg have less impact on log file then Index Rebuild?

3. Should a truncate log and shrink log file be performed after these maintenance commands?

4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?

I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.

Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.

5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?

View 3 Replies View Related

Help Please (Check File Exists/ Archive File/ Check If File Empty)

Mar 10, 2008



Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...


Here is what I want to do:


Check if file exist (C:DTS UpgradeFilexxx.txt) --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)


AND/OR

If there are any good website that have good direction, let me know


Thanks in advance for your help!!!

View 5 Replies View Related

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

SQL Server Admin 2014 :: Creating Additional Data File For A Particular File Group?

Jul 6, 2015

For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.

Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?

View 5 Replies View Related

Integration Services :: Flat File Error File Being Created In-spite Of No Errors

Jun 23, 2015

I have a package in which there are only one Data flow Task and it has only three components. 1) Source , which is a SQL db 2) destination and 3) OLE DB Destination flat file Error output file.   I want the error file to be created ONLY if there is any error while dumping the data into destination DB. But , the issue is, the error flat file is being created inspite of No error while dumping the  data from Source to Destination.

View 5 Replies View Related

Integration Services :: Renaming / Replacing Part Of A File In File System Task

May 14, 2015

I'm copying files to a folder with the naming convention as follows in the source folder:

CM_ABC_MY_TEST.txt

In the destination folder, this filename needs to appear as:

CM_XYZ_MY_TEST.txt

In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.

View 10 Replies View Related

Integration Services :: Get FileName Fo Each File Created Via Dynamic Flat File Destination

Jul 24, 2015

Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?

Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’

E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt  etc} using Foreach Loop Container  :

* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created

*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4

For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:

@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"

All this successfully creates these 4 files:

Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt

Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:

CREATE TABLE dbo.MMMAudit
  (
     AuditID      INT IDENTITY(1, 1) NOT NULL,
     PackageName     VARCHAR(100) NULL,
  
FileName           VARCHAR(100) NULL,
     LoadTime        DATETIME NULL,
     NumberofRecords INT NULL
  ) 

To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:

Execute SQL Task

Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below

SQLStatement: INSERT INTO [dbo].[MMMAudit] ( 
PackageName,NumerofRecords,LoadTime)
 (?,?.GETDATE)

Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?

AuditID PackageName FileName  NumberOfRecords
1           MMM       NULL                      12
2          MMM  NULL                23
3          MMM  NULL      14
4          MMM  NULL              1                     

View 2 Replies View Related

How To Create, Install And Deploy Dts File For Sqlserver 2000 And Dtsx File For 2005 ?

Apr 24, 2007

Hi,



I am looking for tutorials about how to create dts et dtsx files.

Thanks for your help.



Arioule.

View 1 Replies View Related

Script Task To Compare File From FTP With File On Local Archive Behaved Strangely!

May 9, 2008

In my script task I have the following code. The task I'm trying to accomplish is:
If the filename on FTP can be found in the local archive folder of e: drive then show message "FileAlreadyThere" (I will ultimatley change it to do nothing); if the filename on FTP cannot be found in the local archive folder of e: drive then transfer the file to the local package folder on d: drive.

While the script task is executing I was watching it closely, but the problem i saw is that:
If some files on FTP are already in local archive folder and some are not, then it the files which are already in the archive folder are dumped to the package folder; then after that the files which are not in the archive folder are then dumped to the package folder. But I only want the new files on FTP to be transferred to the package folder for further processing.

Then after this is finished, I saw all the files in the package folder are refreshed one after another, after the first round of refresh the second round starts, after the second round finishes it then stopped. I saw it refreshes itself because the 'Date Modified' of the file changes. And I saw the script task turned green.

I don't see how the code below produced this result. Something is wrong in the logic of the loop? Anyone has any idea why it's behaving the way it is now? And how to change the code to accomplish what I want? Thanks a lot!!

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
cm.Properties("ServerName").SetValue(cm, "ftp2.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
ftp.Connect()
ftp.SetWorkingDirectory("/directory")
Dim fileNames() As String
Dim folderNames() As String
ftp.GetListing(folderNames, fileNames)
If fileNames Is Nothing Then
MsgBox("NoFileOnFTP")
Else
Dim fileName As String
For Each fileName In fileNames
If File.Exists("c: emp" + fileName) Then
MsgBox("FileAlreadyThere")
Else
ftp.ReceiveFiles(fileNames, "c: emp", True, True)
End If
Next
End If

ftp.Close()
End Sub
End Class

View 3 Replies View Related

XML Format File For Bulk Insert Of Text File With Fixed Length Columns

Jan 2, 2008

Hey All,

Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.

I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:




Example of Data

0124015Apple Crate 32.12

0124016Bananna Box 12.56

0124017Mango Carton 15.98

0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money

How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?

I don't know if it makes any difference, but I've been calling SQL from my code by doing this:




Code in C# Form

SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;

SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.



Thanks for your time,


Hayden.

View 1 Replies View Related

SQL 2012 :: Find Out Number Of Columns In Flat File Before Process That Particular File

Apr 14, 2014

I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

[code]....

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.

View 4 Replies View Related

SQL 2012 :: TempDB Log File Usage Constantly Rising And File Keeps Growing?

Jun 16, 2014

The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.

I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.

View 9 Replies View Related

SQL Server Admin 2014 :: Can Delete A Data-file Or File-group

Apr 27, 2015

In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?

View 3 Replies View Related

Is It Possible To Write A Format File That Skips A Few Bytes Of Header In Data File?

Jul 23, 2005

Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob

View 7 Replies View Related

Shrink File Tool From EM Took All The Empty Space Allocated To Data File

Jul 20, 2005

I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP

View 2 Replies View Related

Integration Services :: Excel Destination With Run Date-1 Inside File Not The File Name

Aug 26, 2015

I have a ssis package where I need to have excel destination.  In the Excel file, I need to have few rows with some text and then populate data below the text. One the text is like this:

Data as of:  08/25/2015

if the report ran today, then Data as of will have Yesterday. So, if the user opens that excel file after a week, then user should see same  Data as of:  08/25/2015. not today()-day(1).

I was planing to handle on excel side with today()-day(1). but it only works the day it was run. Then the excel file is open after few days later, then it might as Data as of:  08/30/2015 which is not true. It should still stay Data as of:

 08/25/2015 on what ever date the excel file is open. The SSIS package  runs only once. 

How do I handle this so that whenever user open the file, they will see Data as of:  08/25/2015. This is not a column in excel. It is like a description of data in excel.

View 3 Replies View Related

Help Getting Error When Using Operation Rename A File In The File System Task Editor?

Aug 18, 2006

Does anyone know how to do this using variables?  Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001. 

 I also tried it writing a script and still the same error.  If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename.  So the value of the variables will change everyday.  Here is my expression:

@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:Documents and SettingsmroushDesktopOSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

 

View 6 Replies View Related







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