I have scheduled an export of some DBs to mdb- files.
the problem is: everytime the DTS- packet is executing, the records are appended. eg the packet runs 4 times there are 4 records with the id 1, 4 records with id2 and so on.
But I only want a fresh copy of the database, not an incremental update.
please help
Can DTS be used for exporting XML to files?
Thanks for your help.
Les
i have a VB project utilising SQL server Express. I need to export the database files so that the user data can be copied to a laptop and made available to other users.
My problem is this, connections are remaining open to the DB files even though I close all forms that include data access. Typically, my application takes around 6 minutes before the connections are released and the files can be copied.
Is there a way of either forcing all connections to close or of exporting the DB while it has connections?
Rich
I would like to export SQLServer data from a table to a text file within a stored procedure that is fired from an after insert trigger. In Oracle the is a UTL_FILE Package that does this. Is there any way to do this in a SQLServer stored procedure.
View 1 Replies View RelatedHi everybody, i'm new to SSIS, so it's possible that mine is a very stupid question
I have to develop a simple ETL package that reads data from a csv file and writes them to an xls file; the problem is that when the number of rows exceeds the maximum number of rows allowed for an xls file i get an error.
There is a way to solve this problem? for example adding a new sheet or creating a new file?
Thanks in advance
Hi.
I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.
I created a for each loop with an enumerator that returns the table names and sets a user variable.
I created a data flow task which dynamically connects to the table name variable.
In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.
Any ideas?
I am needing to export some files in landscape when I select the PDF option in the dropdown on the ReportViewer.aspx page. How do I do this?
View 1 Replies View RelatedWe just upgraded from SQL 2000 to 2005. Under 2000, I could export multiple stored procs to separate windows files.
Is there a way to do this under 2005 without exporting 1 proc at a time?
Hi,
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
Any takers,
Thanking you in advance.
Bye for now,
Himauhu
I am transferring data from Oracle tables into text files, and facing these errors.
1. I have a varaible working as an expression and my query goes into that variable and onwards that variable is passed to dataflow task, which parse the query. my query is simple saying "Select * from PLS.ABC" where PLS is my schema, but the task generates error "Opening a rowset for "Select * from PLS.ABC" failed. check that the table exists in the database. and surely the table is there.
2. I have a foreach loop that iterates through all the table names and the table names are passed onwards to the varaible query, the dataflow task inside the foreach loop gets the variable query and will generate text files based on tablenames which i have supplied in another variable to the connectionstring property of the flatfile destination. Is it possible or not. all the tables have different columns and i need the output in text files.
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
View 1 Replies View RelatedI proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedIn the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.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") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you
View 4 Replies View RelatedI have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?
View 6 Replies View RelatedI am wanting to reduce the amount of Virtual Log Files I have. In reading through the Online Book Documentation, I realize that I have forgotten to move the Transaction Log Files to a different drive. Now that the server is in production, I wanted to get some input about the best way of making this change.
Can I just change the directory the log files are being written to in the DB properties without having any adverse problems occurring?
Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
I need to write codes to access many image files and wave files and display them on the webpage. Both of these files are stored on the server (as many experts in this forum adviced) and their locations are in the SQL server database. About the wav files, I think I will have to convert them to MP3 first for fast delivery on internet. Since I am a newbie, any help would be appreciated.
Thanks,
I have a problem with bcp and format files.We changed our databases from varchar to nvarchar to support unicode. Noproblems so fare with that. It is working fine.But now I need a format file for the customer table and and it is notworking. It is working fine with the old DB with varchar, but withnvarchar I'm not able to copy the data. The biggest problem is, that Igot no error message. BCP starts copying to table and finished withouterror message.This is my table:CREATE TABLE [dbo].[Customer] ([ID] [int] NOT NULL ,[CreationTime] [datetime] NULL ,[ModificationTime] [datetime] NULL ,[DiscoveryTime] [datetime] NULL ,[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,[Class] [int] NULL ,[Subclass] [int] NULL ,[Capabilities] [int] NULL ,[SnapshotID] [int] NOT NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOTNULL ,[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AINOT NULL ,[LocationID] [int] NULL ,[MirrorID] [binary] (16) NULL ,[DeleteFlag] [bit] NULL ,[AdminStatus] [bit] NULL) ON [PRIMARY]GOand this is the format file:8.0131 SQLINT 1 12 "#~@~#" 1 ID ""2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTime ""4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""5 SQLNCHAR 2 510 "#~@~#" 5 Name_SQL_Latin1_General_CP1_CI_AS6 SQLINT 1 12 "#~@~#" 6 Class ""7 SQLINT 1 12 "#~@~#" 7 Subclass ""8 SQLINT 1 12 "#~@~#" 8 Capabilities ""9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""10 SQLNCHAR 2 510 "#~@~#" 10 CompanyNameSQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountrySQL_Latin1_General_CP1_CI_AS12 SQLINT 1 12 "#~@~#" 12 LocationID ""13 SQLBINARY 1 33 "#~@~#
"13 MirrorID """#~@~#" is the field terminator. We have a lot of text files with allkind of charachers in it. So we think this is a set that will neveroccur in our files.Thanks for your help!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
Is there a way to do this with some sort of task ?
View 7 Replies View RelatedI'm using script task to rename files inside foreach loop files,code like shown below
Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))
dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "" & newname
Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"
I tried to use file system task to perform that,but failed either
Rename file operation in file system only can rename a file in a specified location,who can help me?
TIA
Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
Hi
I built a new sql server database and want to export its content to a .sql file. How to do it? (EM, QA ?) Thanks
I have a SQL database with about 600 tables. Is there an easy way to export all tables into individual excel files?
Thanks,
Ken Nicholson
Sara Lee Corporation
I need to export my data using DTS into a csv text file but with fixed
record length
ie: "data ","more data ","data "
and so on - is there an easy way to do this? - when you setup the
destination text file it appears to be a choice between one and the other.
Regards
James Bevan
Hi, I'm new to SQL sever and am using SQL Server Management Studio Express 2005.
I've written my query that returns too many rows to be looked at Excel, and so wish to view and perform my calculations in SAS.
However, I don't know how to export the data I am getting in to SAS. I have tried using the Results to File option, but when I try import this into SAS it doesn't like the data.
Is there some option I need or is there anyway I can export the data to SAS.
Thanks a lot.
Nesu.
Hi,I have a bunch of stored procedures (some using temp tables) which giveout tables which are then used in crystal to give out reports. Thesestored procs are run dynamically depending on values users select onformsSome people instead of reports want Comma Seperated Files (CSVs). I amtrying to find a good way of outputting to csv with using either sqlserver or ASP. I tried two ways but none of them were ideal1st methodfirst way i tried was creating a record set in asp and then using thefollowing to output the data in a comma delimited row and writing tofile.Response.Write RecordSet.GetString(,,", ",vbCrLf,"")The problem with the above was with data sets greater than 10,000records the processing time increases exponentially because of memoryusage ( i found other people with the same problem)So i tried the following2nd methodI used bcp using the master.dbo.xp_cmdshell command to output thecsv. The problem with that is that the bcp executes the stored procthree times and also doesnot give out column headers. So i had to runthe stored proc once in ASP and get the headers from the record set andstore it to a text file and then run the stored proc in bcp and sendthe output to a csv file. Then merge the two files into a third file.The problem with this method is that it takes around 4 times as long torun as just the stored proceedure and also i have to create 3 filesinstead of one.I would appreciate any suggestions you have to output the csv from astored proc in sql server. Note users have to do this dynamically whenthey submit a form so cant use the query analyzer or enterprise managertools.Thanks for your time and help:)
View 4 Replies View RelatedI have a report which is just a simple table with only the detail row (which then as about a dozen columns)
I have uploaded this to report manger. The report generates correctly and I export to CSV.
But when I open the CSV file there are Headings at the top. I want to know is there any way of getting rid of there headers so it is jus the data which shows.
(The headers seem to be the name of the textbox column in reporting services)
I'm not sure if this is possible or not, still new to RS. I need to export a file in CSV format but a vendor needs the very first character to be a '~'. I've tried adding that to the first column name without sucess and RS won't allow me to name it with the ~ in the properties box. Any ideas? Thanks
View 1 Replies View Related