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 RepliesCurrently looping through the set of flat files like CHK0604, CHK0611, CHK0618, and CHK0625 from the source folder C:SOURCE
OBJECTIVE within the flat file if any records/rows cause error i have to move the bad data into separate folder C:ERROR
STEPS TAKEN
1) In FOREACH LOOP component i specified the variable User:: sourceFilePath for my source file CHK0604 etc. location C:SOURCE. The loop walkthrough each file in C:SOURCE and if no error then moves the flat file into another folder C:ARCHIVED. This task is perfectly working.
2) Within the dataflow I am diverting the the bad rows from "conditional component" into "Flat File Destination" Component.
3) "Flat File Destination" Connection manager i set the expressions as @[User:: sourceFilePath] +"_Error.TXT".
ISSUE
Because of point (3) the error file is created in the SOURCE flat file location C:SOURCE.
QUESTION
1) My error file name should be CHK0604_Error, CHK0611_Error, CHK0618_Error, CHK0625_Error created in another folder C:ERROR.
2) How to move the bad data into another directory while looping through a set of FLAT FILES ?
3) If i have to create another variable like @[User:: ErrorFilePath] where to create ? How to use the source file title as the title of error file.?
Thanks for the help
Hi,
I have created a package and when i was trying to configure a flat file destination, i am getting the following error:
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.
===================================
Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
Please advise.
Thanks & Regards,
Deepak
In my SSIS package I have flat files as a source, I have to load numbers of flat files into SQL target table. I am using For each loop container for that. I am doing it correctly. My aim is to validate the source data from all angle before writing it into target sql table. I am using below points to validate the source data , if I found any bad data I am redirecting those data to error output.
To Checking
1. To check whether data type of column.
2. To check whether buisinesskey column null.
Is there any thing which I am missing to validate source data.
Screen shot for reference
have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say:
exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
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 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.
I 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 RelatedHi,
I have some some files names in SQL DATABASE but my actuall files are keep in a seperate folder. so please help me that becasue i dont know that how i can select the specific files from folder and fetch into the imageArray according to the database table. In my coding its get the all files from directory but i want to get from database with where class and then select from directory.
The structure of my table is
create table event_pic( event_sub_id integer, event_pic_name varchar(50) )
here is code below:
Sub displayMe() dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ") dim cmd as new SQLCommand("select * from event_pic where event_sub_id='5' ",con) con.open() dim SDR as SQLDataReader SDR = cmd.ExecuteReader() con.close() Dim imageArray() As String Dim i As Integer ' grab full path and file of images on server in images folder imageArray = Directory.GetFiles(Server.MapPath("upload/"), "*.*")
' remove the full path from the image filenames For i = 0 To (imageArray.Length - 1) imageArray(i) = Replace(imageArray(i), Server.MapPath("upload/"), "") Next ViewImages.DataSource = imageArray ViewImages.DataBind() End Sub
http://forums.databasejournal.com/showthread.php?s=&threadid=29895
View 2 Replies View Relatedhow can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.
View 20 Replies View RelatedIs there an XP/SP out there that will return a list of files residingin a specified directory?I'm looking for something simlar toExecute master..xp_subdirs N'C:'But instead of it returning a list of subdirs I want it to return alist of files in that directory.Jeff
View 5 Replies View RelatedHello,
How to delete old files stored in a directory using dos script?.
I appreciate your feedback.
Thanks,
Ravi
Hi,
I have posted a request regarding deleting the file more than 1 month old that we have discussed in bleow URL.
http://forums.databasejournal.com/showthread.php?s=&threadid=29895
My new request is I have enabled C2 Audit mode and wanted to delete old files that is more than 200MB.
Could you Please give me a query ?.
Thanks,
Ravi
Hi,
How do I copy files from several subdirectories into the same directory? I have about 80 subdirectories of a parent directory from which I want to copy the files and place the copies into the same, flat destination directory. I tried using the copy file and copy directory file system tasks, but this always seems to recreate the subdirectory structure under the destination directory.
How can I get around this in SSIS?
Thanks in advance,
Bruce.
In SSIS, I was to:
take files from c:directory1
copy them to cirectory2 with a different name (concatenated date on the end)
delete them from c:directory1
Should I be using the Script Task for this? I am wondering if I should be using the File System Task which copies directories or files, but I don't know if I could rename the files during the copy.
Also, I want the directory names to be in the configuration file. I am not sure how to do this. If I set up a flat file source connection, I need to specify a file name and I just want the directory names. How do I get them in the config file and how to I read them into my script from the config file?
Linda
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,
Which is better, to store the files onto the server's folders or to a database?
I tried storing to MSSQL 2000 but the varbinary does not allow me to set "MAX" for the data type.
My problem, I have the aspx, .cs and ascx files but I don t have a project or solution that came with the files (since that was done by front page). So I need to add all those files in a solution or a project so that I can maintain the site that was created by front page using Visula Studio.
Do you know how i Can put all those files in a new project or new solution.
2/Also, when we modify the the pages, how can i put them beck in the site, will I have to compile the files again, or not. thank you.
Thank you
Hi
After install the mdf and log files are stored in the Program Files directory tree.
How do I change the place were they are stored to a path of my own choice, so that new DBs get created there too?
I've looked though the docs, but I can't find this.
Thanks
John
Here's the senario:
View 8 Replies View Relatedwhere in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies View RelatedMy task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7.
DROP TABLE images
CREATE TABLE images
(
image_name varchar(500) null
,image_data varbinary(max) null
[Code] ....
I have a scenario where I need to move a series of files from within a directory of many files. The files follow no nameing convention and are more of less random. However the file names never change from week to week. I tried various different options in a 'file system task', no go.
Any ideas on how to move only a list of files?
or
can I load only specific files into a 'Foreach Loop container' from a certain directory. I tried delimiting file names in the file source, that did not work.
Either way can work,
Thanks
I need to only receive files via ftp when the file does not exist on my local machine. FIles are being added to the remote location on a weekly basis and they are being downloaded locally. I do not want to download all the files each time. Instead, just download what was not already downloaded. Is there a way to do this? I want to do this using SSIS / ftp task.
Thanks in advance.
I have some Large flat fiiles that I need to export to my SQL Server database. The file sizes range from 16 MB to 116 MB. I've tried to save the files to an excel sread sheet and then export them in that format, but that didn't work. does anyone have any suggestions?
View 1 Replies View RelatedHow can I export data from sql server 2005 table to fixed length flat file without using xp_cmdshell option from sql server stored procedure ?
View 7 Replies View Relatedhi all
i using SSIS to import flat files and i need support
how can i import flat file from folder inculed many files and when finish start to next and next .....
if can i select from flat files to add condition
like Select * from.....where ......
thanks
Hello Everyone.
I am a bit new to SQL Server but not to DBA or programming per se. I am having difficulties getting either an Excel or Text flat file to import properly.
I guess it would be best to ask, using either SSIS or BULK INSERT, what options need to be entered for a typical excel flat file?
In my application I am allowing the users attach files. I found the data type "Image", Will this also allow regular file attachments?
Thanks,
Steve C.
How can I store flat files in SQL SERVER??
Actually I am planning to prepare a repository of different files like .xls, .pdf, .doc, .ppt etc and then i will have a web interface to access these files. Can anybody guide me, How can i store these flat files in datbase.
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
View 1 Replies View RelatedHello,
I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.
Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)
I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.
So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?
Thanks!
Lee.