Split The Existing MDF File Into Mutliple Files As A File Group?
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!!
Anyways.. I heard that the general performance woul grow if i had them as "File Groups"..
Is there anyway - to split the existing MDF file into Mutliple files as a File Group?
Where should i start? Can someone please direct me..
View Complete Forum Thread with Replies
Related Forum Messages:
Can I Split A Long .sql File Into Multiple Files?
I have one really long .sql file I'm working on. It's actually a data conversion type script. It's gotten really cumbersome to work on as long as it is. I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks
View Replies !
How To Migrate Existing Indexes To A Different File Group?
Is there any utility to migrate the existing indexes on default filegroup to a new user defined file group? The only way that I know is to delete the index and recreate with new filegroup. How about for table? Is there any utility for migrating the existing tables to a different filegroup? The only way that I know is to create a clustered index on the new filegroup which will move the table to the new filegroup. If a clustered index exists then drop the clustered index and recreate it with the new filegroup I am looking for an existing utility which would do the above? Any help is appreciated. Thanx in advance.
View Replies !
Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it. In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory. Linda Here is the old VBScript Code: Public Sub Main() Option Explicit Function Main() Dim MovementDataDir Dim MovementArchiveDataDir Dim MovementDataFile Dim MovementArchiveDataFile Dim FileNameRoot Dim FileNameExtension, DecimalLocation Dim CurMonth, CurDay Dim FileApplicationDate Dim fso ' File System Object Dim folder Dim FileCollection Dim MovementFile '====================================================================== 'Create text strings of today's date to be appended to the archived file. FileApplicationDate = Now CurMonth = Month(FileApplicationDate) CurDay = Day(FileApplicationDate) If Len(CurMonth) = 1 Then CurMonth = "0" & CurMonth End If If Len(CurDay) = 1 Then CurDay = "0" & CurDay End If FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate) '===================================================================== ' Set the movement data directory from the global variable. MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value fso = CreateObject("Scripting.FileSystemObject") folder = fso.GetFolder(MovementDataDir) FileCollection = folder.Files ' Loop through all files in the data directory. For Each MovementFile In FileCollection ' Get the full path name of the current data file. MovementDataFile = MovementDataDir & "" & MovementFile.Name ' Get the full path name of the archive data file. MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name DecimalLocation = InStr(1, MovementArchiveDataFile, ".") FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1) FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1) MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension If (fso.FileExists(MovementDataFile)) Then fso.CopyFile(MovementDataFile, MovementArchiveDataFile) ' If the archive file was coppied, then delete the old copy. If (fso.FileExists(MovementArchiveDataFile)) Then fso.DeleteFile(MovementDataFile) End If End If Next fso = Nothing folder = Nothing FileCollection = Nothing Main = DTSTaskExecResult_Success End Function
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???
How do I insert data from a flat file or .csv file into an existing SQL database??? Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together... strSvr = "vkrerftg" StrDb = "Test_DB" 'connection String strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;" Dim dbconn As New SqlConnection(strCon) Dim da As New SqlDataAdapter() Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _ "[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _ "VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn) insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event") insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year") insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss") insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss") insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop") insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC") insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info") da.InsertCommand = insertComm Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _ "SET [Event] = @Event " & _ ",[Year] = @Year " & _ ",[Contract Loss] = @ConLoss " & _ ",[Company Loss] = @CompLoss " & _ ",[IndInsured Loss Prop] = @IndLossProp " & _ ",[IndInsured Loss WC] = @IndLossWC " & _ ",[Event Info] = @EventInfo", dbconn) upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event") upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year") upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss") upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss") upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop") upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC") upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info") da.UpdateCommand = upComm da.Update(dsAIR, "TextDB") ************* ANY HELP WOULD BE GREATLY APPRECIATED************ THANKS
View Replies !
How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???
How do I insert data from a flat file or .csv file into an existing SQL database??? Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together... strSvr = "vkrerftg" StrDb = "Test_DB" 'connection String strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;" Dim dbconn As New SqlConnection(strCon) Dim da As New SqlDataAdapter() Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _ "[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _ "VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn) insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event") insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year") insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss") insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss") insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop") insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC") insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info") da.InsertCommand = insertComm Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _ "SET [Event] = @Event " & _ ",[Year] = @Year " & _ ",[Contract Loss] = @ConLoss " & _ ",[Company Loss] = @CompLoss " & _ ",[IndInsured Loss Prop] = @IndLossProp " & _ ",[IndInsured Loss WC] = @IndLossWC " & _ ",[Event Info] = @EventInfo", dbconn) upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event") upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year") upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss") upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss") upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop") upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC") upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info") da.UpdateCommand = upComm da.Update(dsAIR, "TextDB") ************* ANY HELP WOULD BE GREATLY APPRECIATED************ THANKS
View Replies !
Extract Data With Mutliple &"headers&" In 1 File
My query relates to that of Lightbug3 and the solution from X002548. My query is how do I have multiple headers within the export file? I am extracting order information using DTS whereby there is a header containing the purchase order number and the detail being the product ordered for that PONumber. I need to start a new header for each new purchase order number. I'm looking to create something similar to PONumber 1 Product, qty Product qty PONumber 2 Product, qty Product qty Would be grateful for any advice. Thanks
View Replies !
For Each File Enumrator Is Not Exceute Remaining Files If One File Fails
Hi All, I my requirement I need to read all the files from folder (one by one) and insert data from those files into the database table . I am facing one issue here . If suppose while executing if any of the file fails its not executing the other files. Package stops execution. I cannot use Redirect rows option beacuse as per requirment if file has some Data problem , I am suppose to ignore the file instead of Data Rows. Is there is any property in for each file task .....kindly suggest Regards Shagun
View Replies !
Question: How To Run With Mutliple Configuration Files.
Hello, Here is the scenario. I'm using SSIS and SQL 2005 I have two dtsx packages. Lets call them TESTA.dtsx and TESTB.dtsx. I've enabled using XML package configurations on both. TestB.dtsx pulls some data from a table and writes it to a flat file. TestA.dtsx calls TestB.dtsx. For the test I changed the TestB fileoutput connection string to save to a different location. When I run TestA.DTSX using DTEXEC. TestB's configuration file is not being used. How do I kick off TestA.dtsx and specify a package configuration for test b? I tried manually copying the Config parameters from TESTB's config file to TESTA. When I run TESTB directly from DTEXEC and pass in it's config file as an argument it runs fine.
View Replies !
Split The File
my ssis package downloades the text file from Ftp. iT downloades for ex 5 files. I want to split the file to smaller file after downloades. If the size of file is more then 600 mB then I want to split it into 6 files. please suggest if any task in SSIS can perform this or any other way.
View Replies !
Split Txt File Into Multiple
How can I split this incoming file into separate txts. I want to cut out each Header/detail row section into a new txt. What I mean by header/detail row: incoming txt file: http://www.webfound.net/split.txt basically want to cut out each section like this: http://www.webfound.net/what_to_cut.txt http://www.webfound.net/rows.jpg and a kicker...each new txt name must use a certain field (based on x numbers in header row) followed by another field whcih is the date form the header row. somethign like this: SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 59,4) == "HD" + SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 1,8) == "HD" + .txt I need some hand holding here, it's my first time trying to do something so complicated in SSIS 2005. If I can first just get the txt split into multiple, that would be a big help.
View Replies !
Split Single File Over Two Tables.
I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS? TIA, Barkingdog
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
MSSQL DB File Getting Bigger, Can Split Or Reduce?
Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation? i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?
View Replies !
How To Split One Report Into Many File Based On Volume.
Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,). Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees. Can anyone suggest a way to do this€¦ Regards, Krishna
View Replies !
Restoring A DB File To An Existing DB
I am using MS SQL 2005 Express. I have a database with two filegroups, the PRIMARY default filegroup, and then filegroup X. I have a backup of the PRIMARY filegroup only, and am trying to restore it to my database. When I select the .bak file to restore from, I am NOT given any 'backup sets to restore' - I therefore cannot select the filegroup to restore it. Can anyone advise? (NOTE - I have backed up the transaction log on the database I am restoring to already) Thanks MArco
View Replies !
Delete Blank Row In Flat File Destination From Conditional Split
Hello everybody I have one question about deleting blank row on flat file destination from conditional split. I create an SSIS package to filter data from Flat file source. On flat file source, it is Ragged right format and header row delimeter in {CR}{LF} the coulums are devided manulaay using markers. I use only 2 columns divided and send the source into conditional split task and the conditions are given to filter data, when the output from conditional split is placed on flat file destination, i notice blank rows on the output. I want to delete the blank rows so the result data can be displayed continuously in rows. anybody has any idea for this? I know the script task will work but hope to avoid to use script task. Thank you in advance for all the help.
View Replies !
DTS Issue -- Deleting An Existing Excel File Before Using It Again
I'm exporting data out of my SQL Server DB into an excel file. This portion of my DTS package works find however I need to delete the file and start from scratch to get a complete data refresh (this is where I'm stuck). When my package is run I want it to delete the file, then run the appropriate SQL Statement to get the info out, and create the excel doc. This way its a complete refresh of the data I'm looking for. Thanks in advance everyoneRB
View Replies !
Output Result Of Query To Existing File
Hi,I need to output result of my query to txt file. So I'm using -oparameter, for example:osql.exe -s (local) -d database1 -U sa -P sa -i 'c:\queryFile.sql' -o'c:\output.txt'But it clears existing output.txt file first and then outputs theresult, while I need to append the result of my query without clearingexisting file content. Is it possible ?
View Replies !
Creating A Query Showing File Not Existing
This is a SQL question while using Microsoft SMS. I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display. here is the Code that I have for it that shows that the file exists... how do I make it show that the file doesn't exist?... what am I doing wrong? Thanks for all your help in advance! ********************************** SELECT TOP 100 PERCENT SYS.Netbios_Name0, SYS.User_Name0, SF.FileName, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath, SYS.Operating_System_Name_and0 FROM dbo.v_GS_SoftwareFile SF INNER JOIN dbo.v_R_System SYS ON SYS.ResourceID = SF.ResourceID WHERE (SF.FileName = 'UdaterUI.exe') AND (SF.FilePath = 'C:Program FilesMcAfeeCommon Framework') OR (SF.FileName = 'UdaterUI.exe') AND (SF.FilePath = 'c:Program FilesNetwork AssociatesCommon Framework') ORDER BY SF.FileVersion, SYS.Netbios_Name0
View Replies !
Existing Backup File Kept Growing In Size.
I'm having a problem. When I use the SQL query to make a backup of the database, it worked fine. But everytime I use it, the backed-up file's size kept growing in size. Say I have the file, test.bak whose filesize is 450 MB then I run a new backup to overwrite the existing test.bak file, it just end up as 900 MB. If I run it again, it become 1350 MB and so on. Is there a way to prevent that from happening?
View Replies !
Split An Existing Proc Into Two.
I know, sounds weird, but I have an existing stored procedure that helps me create a navigational structure for my website. Table has this structure: uid - int IsActive - Smallint ParentLevel - int ParentID - int Name - nvarchar Description - nvarchar I added an additional column DisplayOrder - int And now I need to add another column named Type - nvarchar for a particular reason The stored proc looks like this. CREATE procedure hl_GetCategories AS SET NOCOUNT ON SELECT [uid], [Name] FROM Categories WITH(NOLOCK) WHERE ParentLevel = 0 AND uid > 1 ORDER BY DisplayOrder -- added the filter for uid > 1 so that we only get actual -- categories and not the reserved blank one. SELECT [uid],[ParentID],[Name] FROM Categories WITH(NOLOCK) WHERE ParentLevel = 1 -- we are only need the level that is one level deep GO And this grabs a list of Categories and 1 Child Level SubCategory that I use to create navigation. However, my needs have kind of changed. I need to split this proc, or create two different procs to do something similiar. I need a list of Categories and 1 Child-Level Down where Type = MainCat (MainCat means a general category) or Type = BrandCat (BrandCat means a category with the item Brand Name) So I can create two different navigational controls. Any idea on how best to do this?
View Replies !
Append Headers / Footer Rows To Existing Text File
Help, I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
View Replies !
How To Replace The Existing Records In The Table From Updates In The Transactional File
Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields. How do i update the row coming from the transaction file in the tables which contain the rows from the master file . to better explain the example is Master File ID Name AGE Salary Transcode TransDate 2 dev 27 2777 Transaction File indicating change ID Name AGE Salary Transcode TransDate 2 dev 27 24444 C 08072007 the ouput should be 2 dev 27 24444 C 08072007 replacing the existing row in the table(updating the whole row) i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row. what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row. Thanks...
View Replies !
Inporting Attached Excel File Into Existing Table Adding Info
Hi all,I have a problem and need some ideas.What I have done: I created a page to upload an excel file into a SQL Server table along with some customer info (from the login, day, etc.). This excel file contains several rows (some of them may be blank) and columns (also some may be blank). The file is stored in an image object.The file will be checked (they want to do it manually, because contents is a problem). If they say it is OK, I want to run a program to add a record into an existing table with the request no. (from the first table, where the object is stored) and all the information available from the filled rows (first row is header). I have a column, which can be checked, if the row contains data or not.Any ideas?I know how to read from and write the contents of the object to a field in the SQL table. Can I use this?Thanks for any idea / code / link.
View Replies !
File Group
I have a table which has about 10 million rows, is it possible to create a seperate file group just for that table?
View Replies !
Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!
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
View Replies !
File Group Decisions
I need to separate files into to specific file groups to spread acrossseveral raid 1 devices. Since database admin is a new concept to me Iwould like to know what is the best way to decide on how to separatethe file groups should I recode perfmon and what should I look for.Most transactions per second, Stored procedures and queries should Ipay close attention to joins just a few guidelines would be of greathelp.Thanx in advance Dave
View Replies !
File Group As Variable
Hi Champs! How can i create a table on an filegroup that comes from a variable? Using EXECUTE?? the following does not work: declare @var char set var = 'filegroup' Create table table] ... ... ON [@var] /Many thanks
View Replies !
Primary File Group Full
Weird. I have an Agent job that populates some warehouse data every night. It's been working fine for over a year. Then it fails with a message that thePRIMARY file group is full. The database is set to Simple recovery, automatic, unrestricted growth by 10% for both the log file and the data file. The drive it's on has 96GB free. The data file is 1.5GB and the log is 2MB and there's not very much fluctuation in the amoutn of data going into it. Anybody have an idea why that would happen? Thanks for any insight,Pete
View Replies !
Create Indexes - Own File Group
With help of others on this group, I've been learning and researchingabout indexes; an area I neglected.I see I can specify which filegroup I wish to create an index, whichthe default is Primary.I have more than one drive in my SQL server where I put data and logson their own logical raid groups.My databases are SIMPLE, so they dont use much, if any logs (none as Iunderstand).I was thinking of adding an additional file to my database and use itsolely for the indexes.Any thoughts?SQL Server 2005 Enterprise x64 SP28 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.Thanks,Rob
View Replies !
File Group Space Error
Hi,Received the following error during index creation of the tables. Thedata & log files are set to 'unrestricted growth' and enough spaceavailable on the disk. Any reasons?___________Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocatenew page for database 'Ultimareports'. There are no more pages availablein filegroup PRIMARY. Space can be created by dropping objects, addingadditional files, or allowing file growth___________ThanksJohn Jayaseelan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
PRIMARY File Group Is Full
Hi there,I've just run some DTS packages on my test sqlserver (Which has limitedhard disk space and memory) and all the tasks have failed, due to'PRIMARY' file group is fullIs there a query or script I can run to resolve this problem??M3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Database File Group Is Full
We have a database that is set to autogrow, 10% increase. I want to know when it will autogrow, like what % left then will it autogrow? Also is it possible for above settings, I can in some way set up an alert that tells me the database file group--not log file is 90 % full? Thanks
View Replies !
|
TRACKER
