I would like to create a command line tools in SSIS which can be used to archive files.
I have 6 files in a dir and i like to zip them up and them move them to an archive dir.
The 6 files all have the same name but the extension is different.
Hi all,
Is there any regulation or law specify how long to keep archived data? 5 years?
or where can i find those info? Thank you
cheers
I have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution
View 11 Replies View RelatedI have Developed ETL Package Which Supplying the CSV File, if I run the package Next time if Same File name is there I need to Rename the that File with Currentdatetime need to move in to Archive Folder. if that File is not exist in that location no need to move the file into Archive file.
View 4 Replies View RelatedDear Folks,
I have a package that calls winzip to extract files(command line usage) in an Execute Process task. The package runs fine if I am logged in to the server. It hangs on the winzip task otherwise. The package is stored on the server (as opposed to the file system) & is run under a proxy account using the SQL Server Agent. I tried adding folder and WinZip32.exe permissions for the domain user who the proxy account was created under to no avail. Any Ideas?
Thanks for your help!
I've been working on an SSIS package trying to load some data and the archive sequence is faulty. I've been trying to load a few tables created in a previous sequence into a local archive file and I've been getting the error "Could not find a part of the path."
The results aren't telling me what it's finding last and so I don't know where to start.
And the source DOES have data in it. It's something between the source and the destination.
Hello,
how may use xp_cmdshell to call winzip with the command,
i wrote this but it doesn't work ;
declare @cmd varchar(2000)
select @cmd = 'c:program_fileswinzipWzunzip.exe c:AttatchmentsTEST_PJ.zip c:AttatchmentsTEST_PJ.zip'
exec master..xp_cmdshell @cmd
Hi,
In my ETL project, l need to extract rawdata using winzip, and the DOS command l use is
c:program filesWinzipwinzip32.exe -min -e c:
awdata est.zip c:
awdata
where -min for minimize and -e for extract, c:
awdata est.zip for source file and c:
awdata for destination. It works fine by using DOS command.
l configure the Execute Process Tasks with the following parameters, i.e.
RequiredFullFileName : TRUE
Executable : c:program filesWinzipwinzip32.exe
Arguments : -min -e
WorkingDirectory : c:program filesWinzip
StandardInputVariable : User::gsRawFile
StandardOutputVariable : User::gsDestDir
Where User::gsRawFile = c:
awdata est.zip and User::gsDestDir = c:
awdata
But it can't work....no error return.
Any idea when l make the mistake?
Thanks.
Regards,
Yong Boon, Lim
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!!!!
Do any one know who to zip the files.
Here is the deal... I am sending tab delimited files from one folder and i want to zip the file once the files are send and then delete those files as i would have backup in the format of zip file.
Do any one know regarding the aforesaid..
ie. folder X has 5 tab delimited files
once i have send those 5 files i want to zip them in another folder (folder Y ) with date stamp and then delete those 5 files.
Hi,
I use an XML source to load an XML file to my db, so i genrate with sucess the xsd file but when i check OK i get an errer which is "Task mismatch of data streams [Source XML [1]] The XML source adapter does not support the model of mixed content on complex types",
"The component pipeline returned error code HRESULT 0xC02092A1 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)".
I've been tasked with coming up with a process to delete old files out of a certain directory. It's prefered to have a configuration file to go in and be able to change the number of days to retain files (7 for delete files older than 7 days for example). I've been told I can do this with SSIS. Can anybody point me in the right direction to do this in SSIS or even in DOS? I don't know ActiveX scripting if that's what's needed in SSIS. I prefer doing something like this in DOS but not sure it's possible to automate. Any help is appreciated.
Van
Hi
I've recently started creating SSIS packages using Visual Studios. I am trying to find a way of zipping and archiving files in one of the packages. Is this possible?, and if so, is it possible to date stamp the zip files to?
Thanks
I would like to have a SSIS package which loops through each xml file (.xml files) in a folder on the network. And then for each file pull out the data and insert into a sql server table.
Please kindly guide me through this i.e. What task(s) are required, etc.
Thanks
I'm downloading zip'd files and would like to loop through each file that was downloaded. I'd also like to unizip each file and append all of them to one file. I have a dos batch that is fairly simple and would like to emulate it using ssis. Here is what the dos batch file looks like.
DATE /T >%TEMP%D.txt
FOR /F "usebackq tokens=2,3,4 delims=/, " %%i IN ("%TEMP%D.txt") DO SET fname=TAMF_162%%i%%j%%k-%%k.zip
ECHO xxx>zzzzz
ECHO xxxxx>>zzzzz
ECHO BINARY>>zzzzz
ECHO GET %fname%>>zzzzzz
ECHO QUIT>>zzzzz
FTP -s:zzzzzzz ftp.aaaaa.com
PKUNZIP -o -xxxxxxx downloadedfile_1~1.ZIP
DEL TAMF_1~1.ZIP
DEL zzzzzzzz
EXIT
Folks -
Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.
The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.
Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.
Thanks.
Hello,
I am having a bit of trouble with SSIS Configurations.
- In BIDS, I have added a configuration file and specified the properties I want to expose.
- When I build the project, I get the standard bindeployment folder which contains the package file (.dtsx), the configuration file (.dtsconfig) and the deploymentmanifest.
- Before deploying the package, I edit the config file to have the settings I want it to for the environment I am deploying to.
- The package deploys OK
- When I work directly on the SSIS server (64 bit), I can go into SQl Mgt Studio, choose 'run package' and when I look in the connection manager window all the settings are as I desire ( I havent had to add a configuration file manually)
- When I work on a client machine, I connect to the SSIS server and choose 'run package' - the properties/connections are the same as on the server but the values themselves are completely different.
Why is this? Why when I run the package from the client (32 bit) do the configuration values appear to be completely different? How can I run the package remotely and pick up the configuration values that I deployed with?
Or have I misunderstood this whole configuration function?
Please, please help!
Mgale1
Hi,
I am using SSIS for sending E-Mails based on certain transactions in SQL Server. I need to dynamically populate the E-Mail content as the content some times needs to be in Japanese text. I would like to use resource files for this. Could anyone please help me on using resource files in SSIS.
Thanks in advance.
We recieve about 4 million rows of data from a client per week. Each file is a complete snapshot of their data, we need to be able to find the rows that are different or new in the current weeks file compared to last weeks file.
Is this possible using 2 Flat file sources and some kind of sort/merge join?
If you are familiar with ORACLE or postgres its similar to a "MINUS" or "EXCEPT" respectively. Right now we load both files to separate tables and compare them in sql server which is very slow.
I am wondering if it is possible to archive records out of a SQL Server Database. The records would then be removed from the original database. This process should be able to be run multiple times (with user interface).
View 2 Replies View RelatedHi Everybody,
I'm working on a new database of 25GB in size with an expected 25% growth per year and an estimated 1000 TPS. Since I need to retain the old data for atleast 7 years. I would like to know whether I should Archive the database or is there any other means of storing the data, which will be used just to generate reports. Also pl. do let me know the advantages/disadvantages.
Thanks
Kishore
Hi Everybody,
I'm working on a new database of 25GB in size with an expected 25% growth per year and an estimated 1000 TPS. Since I need to retain the old data for atleast 7 years. I would like to know whether I should Archive the database or is there any other means of storing the data, which will be used just to generate reports. Also pl. do let me know the advantages/disadvantages.
Thanks
Kishore
hi,
I need a script to deletes records from my DB older than 30 days.
is their anywhere a script that can help me ?
how should i write ?
using some quesy tool ?
thanks
idan
Project assigned to me called ''Archive off old data''.As SQL DBA the best way to archive large table is data Partitioning. But one table does not enough in terms of size to do partition and plus table has 32 other tables,view,sps dependencies.The data from that table needs to archive off every week to different database on different server. My question are
1) what is the best way to archive data ?
2) what are steps I need to follow ? Do I need to remove dependencies first and then take out data?.
Hello, everyone:
What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle?
Thanks.
ZYT
A client of mine just asked about Archiving and Purging their data (based on different time constraints). For isntance, certain data would get archived after 3 years over to a different database, others purged all together. My thoughts off the bat are store procedures run on a job schedule.
Any other thoughts or ideas?
Is there a way to archive my databases which are not in use so that i can reduce my disk space and i should be able to use them sometime in future?
let me know
thanks
http://blogs.conchango.com/jamiethomson/default.aspx has a lot of great tid-bits for SQL 2005. I am currently on a tight deadine for 25 SSIS packages that need to be able to move from Dev to QA to Staging to Prod. For the life of me I cannot get any of the packages to *READ* the config files created with the package config wizard. All I want to do is move the connection string out of the package so we can change the config file and not have to touch (hand edit) each package. Any help is appreciated!
View 13 Replies View RelatedHello everyone - wonder if you can help.
How do you process all files in an FTP directory - similar to the for each loop for files.
I need to be able to download each file and then move it to an archive folder on the ftp site.
Might also want to do some things in between.
I have a feeling it means getting the directory listing into a recordset or variable then enumerating that.
Sounds like a common requirement and would be quite easy by other means. Can't help feeling that SSIS has built in tasks to do it.
Hi all,New to SQL Server - trying to create an SSIS package that will look forand import a series of Visual Foxpro tables (.DBFs) when they appear ina folder.The tables are/can be all different fields, field widths, etc. Withquite a bit of overlap though.The end result should be table "ABC.DBF" is pulled into SQL Server astable "ABC"Using: SQL Server 2005 Enterprise, SSIS, *latest* version of VFPOLEDBdownloaded from MSI have set up a package and tested it with several different tables andit works great - but I have to redo the data source and destinationeach time...I need to get this to be a somewhat automated process, pulling in all..DBFs no matter what they contain.Can I do this with SSIS alone (and variable substitution) or do I needto write a bunch of code...Thanks very much for your time and thoughts...
View 1 Replies View RelatedI have created a DTS package which imports text file into single sql server table with 8 columns (SourceData). The DTS package uses 'Test1.txt' file. Now i have around 200 text files (Test1,Test2,.....Test200). I need to import them one by one into 'SourceData' table. Could you pls. help me out in getting solved.
Thanks,
Hemal
I could not find the exact details on how to create a SSIS script that would ftp files on these forums, so I am adding my code to help save time for anyone else that might be wanting to do something similar. Here is the VB code for my script task to FTP files (hope this helps someone):
' 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
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, "Enter your Server Name here")
cm.Properties("ServerUserName").SetValue(cm, "Enter your FTP User Name here")
cm.Properties("ServerPassword").SetValue(cm, "Enter your FTP Password here")
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()
'Build a array of all the file names that is going to be FTP'ed (in this case only one file)
Dim files(0) As String
files(0) = "Drive:FullPathYourFileName"
'ftp the file
'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task.
ftp.SendFiles(files, "/Enter Your Remote Path", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII
ftp.Close()
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Hi,
I'm trying to join outputs of two text files combined in to one text file. The lenght of both the input text files are same (590)
For example,
If contents of text_file_1.txt is: aaaaaaaaaaaaaaaaaaaaaaaaaaaa
and contents of text_file_2.txt is: bbbbbbbbbbbbbbbbbbbbbbbbbbbb
My final text file should be :
final_text.txt
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
I use flat file source for both the input file and then use "Union all" and then write out to final text file destination.
The issues is:
The second file (bbbbbbbbbbbbbbbbbbbbbb) is coming up first in the output and then there is no carriage return.
My output looks like:
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb...(goes up to 590)aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaa
What is that I'm missing here. Could some one plese help.
Thanks
Sri