Unzip File Using SQL
Feb 4, 2005Good Day
How do I unzip a file osing SQL command
ADVERTISEMENT
How Can Unzip File Text File Using SSIS
Oct 1, 2006
Hi,
I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.
Unzip File Using C# Code And Asp.net
Mar 7, 2006hello
my problem is how to unzip the file using the c# and asp.net
code
How To Unzip A File Whith Transact Sql
Feb 19, 2004Do U know how to unzip a file with T/SQL?
thanks in advance,
Unzip A File In SSIS Package
Feb 21, 2008I want to unzip a file through "Execute Process Task".
For compressing(zip) a file i write the following.
In Process Tab:
In Executable: C:Program FilesWinZipWINZIP32.EXE
In Arguments: -min -a "C:file.zip" "C:file.mdb"
What arguments should i write to unzip a file?
And in case i want to copy a file from one location to another through Execute Process Task what should i do?
Please Help
How Do You Unzip A File In An SSIS Package?
Jan 24, 2007Is there a way to unzip files in a SQL Server 2005 SSIS package. I now I can do it using winzip and executing the procedure, master..xp_cmdshell, but that is not what I am preferring at the moment. Anything direction in this regards will be much appreciated.
Thanks,
Monisha
Unzip A Sample.txt.z File Using Script Task In SSIS
May 26, 2008
Hi,
I get a "sample.txt.z" file from an ftp site. My work is to unzip the file and load it into SQL Server Database.
Please give me VB Script to write in the SSIS Script task. to unzip the file.
Please help
i have posted the question in many blogs and i could not get the right answer.
Thanks in Advance
How To Unzip Using T-sql
Mar 7, 2008HI All,
I need your help, i want to Unzip files that i downloaded from FTP. i want to unzip using T-sql or SSIS, especially using T-SQL, i am working on sql server 2005, my FTP dump is C:FTP, under this FTP folder i have different zip files, example:
C:FTPFile1.zip
C:FTPFile2.zip
C:FTPFile3.zip
etc, under this zip files there are about 13 text files, thus i need to unzip only C:FTPFile3.zip using T-SQL, how do i do it, please quick response, your response will make my weekend free of headache. Thank you in advance for your help and time.
Thanks,
UnZip TextFIles
Oct 25, 2007
I have huge text files coming from the other end.But they are sending as .zip files and when unziped i find .dat.
Is there a way to set up such that my package can unzip those files and change .dat files to .txt and then start working with with those files.
Please let me know thanks
How To UNZIP Files?
Mar 9, 2006I am trying to unzip some files that I have ftp'ed into a local directory.
I use a foreach file container. Into this container I have placed an Execute Process Task. The EPT runs pkzipc with the following arguments set: -dir=current -extract.
The standard input variable is set to the output variable of the foreach loop.
When I run the package, a console window will open for what I assume is the PKZIP process, but there is nothing printed out in the console and the console won't close unless I force a timeout.
When I enable a timeout I get the following error:
SSIS package "Package.dtsx" starting.
Error: 0xC002914C at Execute Process Task, Execute Process Task: The process timed out.
Task failed: Execute Process Task
Warning: 0x80019002 at Foreach Loop Container 1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
So I have two questions:
1) Is this the best way to unzip files?
2) If it is, what am doing wrong?
Thanks
Unzip Using Ssis
Jul 6, 2007How is it possible to unzip a file using ssis please?
View 1 Replies View RelatedAutomated Unzip
Apr 15, 2008
Hi,
how would i unzip a password protected file after downloading it from sftp server.
thanks
Unzip Or Notify Failure
Oct 18, 2007I have two question
1.In SSIS is there a way to unzip the text files
I have set an automated process in sql server agent
I receive ziped text files.I want to unzip the files before loading the data
2.Is there a way to notify when an error occurs in the package in sql server agent.
Please let me know
Zip,unzip,restore Logshipping
Feb 6, 2008
Hi
We are planning to setup log shipping model being setup between two sqlserver 2005 enterprise edition.
Our transaction log backup sizes are not consistent through out the day.The following is the scenario.
T1 at 8.00 -- 315MB
T2 at 8.30 -- 152MB
T3 at 9.00 -- 2.5GB
T4 at 10.00 -- 500MB
The bandwidth available is 25MB/minute. We are taking the backups of Tlogs at 30mins interval.Now at 'T3' log transfer we want to zip the file on the primary server,copy the file over the WAN and unzip it standby server.
Now i wanted to know whether to perform this kind of action(Zip,copy,unzip), can i create a separate job on primary ?? which will put the unzipped file on standby and allow the log shipping job to restore the log file (T3)
Also i wish to carry my T3,T4 etc backup -- copy-- restores through my Log shipping activity.
I cannot take 15mins backup on primary server to reduce the transaction log backup size.Thts another constraint i have.
Also tell me how to zip and unzip thru command line if u hve any link.
Please advice me
thnks for the help in advance
Regards
Arvind L
SSIS Package For Unzip Files Through Job
Oct 10, 2007
Hi,
I have created a job that will execute a SSIS package which will unzip some zip files. For unzipping we are using WinZip. In the package I have used a .Net script task for unzipping. This script is using WZUNZIP. When I am executing the package directly it is unzipping all the zip files. But when I am executing the job that will execute the SSIS package for unzipping it is going on with the execution and not unzipping the zip files. So finally I stopped the job.
Can you please help me to resolve this issue?
How To Load And UnZip Files From FTP Directory
Mar 5, 2008
HI All,
I have Uzip Files to be loaded From ftp.companyname.com, the zip files get updated everyday thus i have to load the newly added files, the Zip file has got 13 text Files within it, the issue is:
1. how do i load it for the new zip file only, the zip files are shown below, i am trying using FTP Task, but need more info.
2. How do i UnZip it and and take the text files and then load them to sql server 2005, each text file has to be loaded to sql server tables.
3. How do i automate it, i mean every time i run the package (on Job based) it has to look the new file only, see the zip file below to understand what i am saying about.
02/25/2008 09:02PM 2,780,729 CompanyName_2008-02-22.zip
02/27/2008 09:04PM 1,274,557 CompanyName_2008-02-23.zip
02/25/2008 08:57PM 1,383,112 CompanyName_2008-02-24.zip
02/26/2008 04:21PM 3,327,882 CompanyName_2008-02-25.zip
02/27/2008 08:44PM 3,623,334 CompanyName_2008-02-26.zip
02/28/2008 05:27PM 3,570,243 CompanyName_2008-02-27.zip
02/29/2008 04:20PM 3,444,375 CompanyName_2008-02-28.zip
03/01/2008 04:17PM 2,655,782 CompanyName_2008-02-29.zip
03/03/2008 04:09AM 1,179,338 CompanyName_2008-03-01.zip
03/03/2008 05:21PM 1,267,777 CompanyName_2008-03-02.zip
03/04/2008 04:49PM 3,238,121 CompanyName_2008-03-03.zip
As you can see the Zip file names are the blues color, they are added at different time, thus what i need is only the current Zip file (i mean i have to Load only the newly added Zip file (recent one)). Thus the SSIS Task has to go to this FTP server and look the newly added zip file. After that i have Unzip it b/se i have 13 text files there, and then Load them to Sql Server 2005.
Please get help on this, the deadline is near by time, if possible try as soon as possible, I would like to say Thank you for every help you do and try.
Thanks,
Execute Process Task To Unzip In Foreach Loop
Jan 8, 2008Hi,
I am trying to use SSIS.Execute Process Task within Foreach loop . The task should unzip a a variable gzip file provided to it from the previous task
to execute the command from cmd C:Program FilesGnuWin32ingzip.exe -d -k c:folderfilename
My input in the Execute Process Task as follow:
The command line: C:Program FilesGnuWin32ingzip.exe
Argument: -d -k c:folder
Variable: User::var_filename
keep getting error
The process exit code was "1" while the expected was "0".
Thanks
Integration Services :: Unzip Files Using Execute Process Task
Jul 6, 2015while i am trying to unzip files using execute process task ,getting below error
[Execute Process Task] Error: In Executing "C:Program Files7-Zip7z.exe" "a -tzip D:excel.zip D:unzipfileexcel.xls" at "", The process exit code was "1" while the expected was "0".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
i want to know more about unzip and zip files and folders using execute process task.
zip folder: C:Program Files7-Zip7z.exe
SQL version: SQL server 2008 R2
do not having win rar so please instruct using 7z.its quite interest to work but i don't know to get desired result.
SQL Server 2008 :: Unzip Using Execute Process Task Success But Getting Error Message In The Email?
Jun 17, 2015Exec Prcoess task with source : ftp
destination :ftpunzip
work directory ftpunzip
executable : c:Program FilesWinZip
i am using expressing.
It is doing the unzip but getting this error
package process on server server1 has failed within the Task 'Unzip Files' with the following errors:
>
> File/Process "WZUNZIP.EXE" does not exist in directory "c:Program FilesWinZip".
This is the error message i am getting it
Reporting Services :: Parsing SSRS Config File And Dynamically Changing File Path Of Config File In Code
Sep 2, 2015Currently 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.
File Will Not Attach To SQL Reason File Is Not Primary File
May 10, 2007I 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
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, 2007Greetings, 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
Attach Mdf File And Data File Without Log File
Mar 1, 2006how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
Maintenance Commands Affect On Log File / Log File Maintenance Without Log File Backups?
Jun 18, 2015I 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?
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!!!
SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?
Jul 31, 2014I 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.
SQL Server Admin 2014 :: Creating Additional Data File For A Particular File Group?
Jul 6, 2015For 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?
Integration Services :: Flat File Error File Being Created In-spite Of No Errors
Jun 23, 2015I 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 RelatedIntegration Services :: Renaming / Replacing Part Of A File In File System Task
May 14, 2015I'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.
Integration Services :: Get FileName Fo Each File Created Via Dynamic Flat File Destination
Jul 24, 2015Need 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
How To Create, Install And Deploy Dts File For Sqlserver 2000 And Dtsx File For 2005 ?
Apr 24, 2007Hi,
I am looking for tutorials about how to create dts et dtsx files.
Thanks for your help.
Arioule.
Script Task To Compare File From FTP With File On Local Archive Behaved Strangely!
May 9, 2008In 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
XML Format File For Bulk Insert Of Text File With Fixed Length Columns
Jan 2, 2008Hey 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.