Whats The Best Way To Store Filename / File Path?
Jan 20, 2004I'm guessing the best way / only way to store a filename is to store it as a varchar?
I'm using it as a reference to pictures wefor a gallery
ADVERTISEMENT
Photos In SQL Server: Store As Images Or Strings With Path && Filename?
Apr 4, 2008Hi, for my photo album database I'm trying to decide whether to store the actual photos as images in the db or whether to leave them in file directories with just the path & filename in the db. As a beginner in this field I don't know how either of these options compare (e.g. in terms of performance, complexity, flexibility and maintainablity) so any advice on the pros and cons of each would be appreciated.
View 5 Replies View RelatedDirectory Structure Not Known Then How To Provide File Path For A FILENAME Param
Feb 19, 2007Hi,
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Warm Regards,
Mushq
How To Concatenate Variables For RAW Output Filename I.e.@path + @filename?
Sep 7, 2007Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
How To Load XML From Filename And Path
Sep 6, 2005Hi,
I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.
I need to join the data in the XML with rows in the "Resources" table (and other tables).
Is it possible to get a Stored Procedure to load the XML file into a temporary table?
Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?
What is the best approach? (I know nothing about XML in SQL Server yet)
Thanks in advance,
Chiz.
Extracting Filename From Path
Nov 8, 2013I have to extract only the filename from the below filepath.
The first part prodserverexport always remains the same. A new folder is created prodserverexport 20071001, prodserverexport 200710012 etc each time the file is dropped . I want to extract only the part after that namely 20071001-200001-e430f8fd-PREC272.wma and like wise and dump it into a table.
prodserverexport2007100120071001-200001-e430f8fd-PREC272.wma
prodserverexport2007100220071002-013203-d00b2007-PREC120.wma
prodserverexport2007100220071002-032038-18381b92-PREC207.wma
prodserverexport2007100820071008-144758-16359a7b-PREC224.wma
prodserverexport2007100920071009-170426-4ed3535c-PREC97.wma
Whats The Best Way To Store Multiple IDs?
May 17, 2008I'm working on an mini intranet site for a "University" type style. I have a table called "events" which stores information. One of my columns is "eventRSVP" which stores the employees ID's who click on the button, separated by a comma.
example:
Quote: 1751,2179,1371,2314,1778,702,1607,798,2097 Would it be better to create a new table called something like "event_jn" and it has 3 columns.
Quote: ---------------------------
| jnID | jnEVENT | jnEMP |
---------------------------
| 1 | 3 | 2097 |
---------------------------
I'm also redoing our whole intranet and would be doing a lot of these similar type id linking. So my other question is, the more tables I have, is it going to slow down the response time?
Appreciate any insight that can be given!
TIA!
Store A Filename In A Database
Dec 13, 2006how can I store a filename in the database, i have some GIF files which i want to show up on report, how can i specify those filename with a path in the database.
i tried manually entering the path c:Dataimages.gif but it didnt work. Can someone pls tell me how to do it.
Regards,
Karen
Integration Services :: How To Store Details From FileName Into Variable Using SSIS
Oct 19, 2015I have one package through for each loop container I am loading all flat files into target table and its working fine.Now I my requirement is to capture the .txt file and loading into sqlserver table.Please check below file name.
CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt
from above file name I have to derived below detail.I have variable @File_Name which is used to stored the file name at rune time.Now I want to derived 5 values from the file name as below
Clientname=CCCLOC
Partnername=DDDLOC
Entity=LOC
Creationdate=20151203
Creationtime=240000
how to write expression for above situation.
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
TO FILE Filename Question
Feb 14, 2008Hello everybody
I was just wondering if anybody knows the correct way to write a query that will output to a text file in 2005
Right now i'm doing
select * from tablename TO FILE 'c: hefile.txt'
But I keep getting a syntax error near TO
All the examples i've seen use this but i don't think they are for 2005.
Any help will be appreciated
Thx
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.
DTS Pakcage File Import But Filename Changes
Aug 5, 2004Hi All,
I am trying to automate a rather complicated file import routine using a DTS package. I'm getting on ok with it but have now reached the limits of my knowledge.
At the moment I am sent a file every month e.g. "perf04-05m1.csv". I then rename it to "import.csv" and run the DTS package. I would like to make the process more dynamic so that the the DTS package takes in the most recent file no matter it's filename e.g. I don't have to re-name the file to "import.csv" in order for the DTS package to run.
I have had a look at BOL and think that I need to use either a Dynamic Properties Task or a Global variable but I'm not sure how to tie it all together, can anyone offer any pointers or know of any good articles I can have a look at. I may also need to dynamically alter some of the SQL statements within the DTS package as well.
Cheers
ForEach File - Filename Mask
Dec 20, 2007
Hello
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT.
It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
newstar1860
File System Task - Move File With Dynamic Destination Path
Oct 9, 2006I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(User::FileDestinationPath?).Value = dts.Variables(User::FileDestinationPath?).Value & ? Month & _? & Day & _? & Year & ?) which gives me C:TestFiles 9_14_2006?.
File System Task (within For Each Loop, second step) This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that days date. Basically, how do I get this to work since I cant hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the Variable FileNameAndLocation? is used as a source or destination and is empty.?
Let me know if I need to clarify further...I may be missing something very simple. Any help would be greatly appreciated!
Integration Services :: Network Path For Flat File Destination - Cannot Open Data File
Apr 6, 2015I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me error " cannot open data file" ...
Nothing is wrong with package.
File Name Includes File Path In DTS Send Mail Task - Help
Jan 26, 2004I have an issue with a DTS package. We create a zip file and then attach it to emails going out using DTS. The problem is that the attachment, when received, is named using the full path to the file, so it is quite long.
Has anyone seen this before? Is there a way out of this?
I am considering mapping a drive to the share holding the file to be named, but the fact is this will shorten the name but will still result in the path being included.
I am wondering if this is a bug, as I suspect this isn't the default behaviour.
Thank you in advance.
Dave
Config File Path Not Being Updated In The Dtsx File While Deployment
Sep 21, 2006Hello All,
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
Thank you in advance for your help
Sumesh
Assign Variable For FileName Of Source File
Aug 10, 2006I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.
I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.
Any ideas or suggestions?
SSIS: Flat File Source With Wildcarded Filename
Aug 14, 2007My task is to write an SSIS package that picks up just one file from a directory and loads it into a database table. The filename is defined as being "ABC*.txt". So I must pick up only one file that matches that wildcard.
I can see two ways of doing this, but I can't get either to work:-
1. Use a Flat File Source connection and put the wildcard in the ConnectionString.
i.e. ConnectionString = "C:\mydirABC*.txt"
But SSIS doesn't seem to support that.
2. Use a Foreach Loop Container with a Foreach File Enumerator, and configure the enumerator as:-
Folder = c:mydir
Files = ABC*.txt
This works well, but loops round for as many files match the wildcard. Is there any way of forcing it to drop out after the first time round the loop?
Or am I missing a much easier solution?
Thanks.
Raw File Destination Access Mode Filename From Variable Problem
Dec 31, 2006I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:Test.txt.
When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\Text.txt" ... so apparently XMLA is adding the escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.
What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.
Change Filename For Flat File Connection Manager At Runtime
Jun 29, 2006I need to add the current date to the end of the filename of a flat file at runtime.
This was previously done with ActiveX script in SQL Server 2000.
oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName")
oConn.DataSource = sNewFileName
oConn = Nothing
I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005.
Thanks in advance!
Regards,
Sara
How To Save Filename As Columnname In Flat File Dest Config
Oct 20, 2006I have created a SSIS pkg (datasource and flat file destination)
I need to have the flatfile renamed after each exec to a column name that exists in the table I am running the pkg against.
Where does the code get put to make this change after the file has been created?
Basically if I could run T-SQL after the file creation, I could make it work.
thx..
File Filename.mdf Is On A Network Device Not Supported For Database Files
Nov 10, 2007
I am trying to attach to aMDF and LDF file that are on the remote location. When you try to do this though getting an error..
"File filename.mdf is on a network device not supported for database files."
How to do this.
Could you please help me to sort out this problem.
Thanks in advance
WillSmith
mail2willsmith@yahoo.com
Log File Path In Configuration File Does Not Work.
Jan 25, 2007Hi,
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
- <Configuration ConfiguredType="Property" Path="Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:APPSBACIARSalesforceConnectSalesforceConnect.log</ConfiguredValue>
</Configuration>
Appreciate a help.
Gulden
How To Pass Filename As A Dynamic Variable To Flat File Source In SSIS ?
Sep 3, 2007Hi,
I am migrating one of my DTS package to SSIS.
My task is to read the filename from a database table and transfer the flat file data in to a table.
In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?
In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source.
Any help ?
Thanks,
Ravi
Using ForEach And Pass Filename To Flat File Source In Data Flow Task
Mar 11, 2008How do i use the foreach loop container and pass each file found according to a specified pattern to a Flat File Source in a Data Flow Task Object so i can operate on each file found in the foreach loop object instead of having to specify a static file name
Thanks
SSIS Execution Error: FlatFile Connection Problem - FileName &&<fileName&&> Specified In The Connection Was Not Valid.
Apr 19, 2008
Hi Champs,
Scenario Configuration : VB.net 2005 Code, WebService for Executing SSIS on Server, SSIS deployed on the Database Server
Problem Description : We are developing windows applicaiton in which we call webservice which was deployed on the same server where SSIS packages are deployed.
Now from Code we are passing FilePath name in variable and execute the Package. But the SSis result says that
The file name "\computernamefol1fol2fol3fol4abc.txt" specified in the connection was not valid.
More Information:
1. Full Permission are given on this network folder.
2. Package executes successfully from SSIS development solution (BIS solution)
3. Deployed packed executes successfully from the Database Server.
4. From Development pc packege executes successfully.
5. Other packages deployed on the same server executed suucessfully with same configuration and scenario.
Only this package is not executing.
-- the only differece with this package with other is -
using ".txt" extension in Flat file connection and using VB Script task
Can any one suggest the appropirate solution for this problem...
Thanks
Tarang Pandya
DTS File Path
Oct 4, 2001How do I dynamically change the file path for the source file in DTS?
Can i use global variable?. if so , How do i use it in the connection task.
Appreciate your help.
Backup Master Key, Cannot Write Into File 'c: Empmaster'. Verify That You Have Write Permissions, That The File Path Is Valid.
Jul 12, 2006Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
Getting Access Denied To FileName Error When Using The Execute Sql Task (With File Connection) Into A Foreach Loop Container.
Jan 18, 2007Hi ALL,
Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container.
Please Note :
I have a folder containing .sql files. I have to dynamically loop through the files and send them as a File connection Folder to the Execute Sql Task.
When I run this Package I am getting the follwoing error :
[Execute SQL Task] Error: An error occurred with the following error message: "Access to the path 'C:ProjectsFuzzy Lookup DataFlow ExampleScripts' is denied.".
Also I have logged in to the machine as Administrator and to Sql Server with sa.
Please help.
Thanks.
Regards,
Salil
Passing A File Path To A SP
Apr 3, 2006I'm trying to write a SP thataccept in input a parameter with the nameof a file (with complete path)but I noticed some problems....It's right this way? Thanks!set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[BI]@FileToImport nvarchar(100)ASBEGINSET NOCOUNT ON;DECLARE @SQL nvarchar(200)SET @SQL = "BULK INSERT tmptable FROM '"+@FileToImport+"'"EXEC (@SQL)END
View 2 Replies View RelatedFOR XML PATH + File Structure
Dec 6, 2006Hey there,I used the FOR XML PATH feature but the XML result is in only one line,it does not conserve the XML structure (treeview). When I open the XMLfile with IE, it s working very (I have the XML hierarchy) but when Iuse Visual Studio or other tool to edit it, I ve just one line!For example:<children group=""><child firstname="" lastname=""></child>=""><childfirstname=""lastname=""></child>..............................</children>............And I d like to have<children group=""><child firstname="" lastname=""></child>=""><child firstname="" lastname=""></child>..............................</children>..............To give you an example of the query:SELECTChild.group AS '@group',(SELECT firstnameAS '@firstname',lastnameAS '@lastname'FROM Collecte_Data_Extract cdeFOR XML PATH('child'), TYPE)FROM Feed AS ChildFOR XML PATH('children'), TYPEAny idea?Many thanks in advance
View 3 Replies View Related