Is there an XP/SP out there that will return a list of files residing
in a specified directory?
I'm looking for something simlar to
Execute master..xp_subdirs N'C:'
But instead of it returning a list of subdirs I want it to return a
list of files in that directory.
Jeff
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 neet save user login form active direcotry to databases. How I can make that?
View 1 Replies View Relatedhow do I list contents of file directory in the SQL Query Analyzer
View 1 Replies View RelatedI need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 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 anyway,can we find the list of servers by querying at active directory?
View 3 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,
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
View 2 Replies View RelatedWhich 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 RelatedI 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 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] ....
Currently 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
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.
Is there an "easy" way to execute a number of .sql files without opening and executing each one ?
View 1 Replies View RelatedHi,
I'm trying to list all database locations with SQL command, but getting lost here :(
So, anyone any idea how to get databasefile locations from server?
All I need is like: 'C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf' and others.
Thanks.
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View RelatedGot following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)