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
How 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
Hi 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
Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Many Thanks
How do you sort files from the Foreach loop container?
I love this component, except I can't get it to sort by filename.
Nice to know how to sort by timestamps also.
Dave
We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck.
The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?
John
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'affinity mask', N'0'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity mask' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Hi,
I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.
I have this enumerator in the c:Program FilesMicrosoft SQL Server90DTSForEachEnumerators folder.
Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.
Please help me.
Also information on how to regeister unmanaged enumerators are welcome!
I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.
However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'
A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.
I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg
"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"
It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.
The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.
Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.
Ever since installing SQL Server 2005 SP1, when using the Foreach Loop
container in SSIS the Foreach File Enumerator and Foreach Item Enumerator are
missing from the Enumerator drop down box. Anyone else seen this issue? and
know how to fix it?
thanks
Eric
Need 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
I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
I want to enumerate all *.xls and *.csv file. How to fill the Files box? I tried
*.xls, *.csv
*.xls *.csv
*.(xls|cvs)
all doesn't work
Hi,
I'm struck with a small issue.. would be great if somebody can help me out. Here is te scenario
1. There would be more than one CSV files in INPUT folder.
2. I'm using a Foreach Loop file enumerator to loop thru the files and load the data into database.
3. If loading is successful the file need to be moved to ARCHIVE folder and next file needs to be picked up for loading
4. In case if there is an error in loading the file has to be moved into ERROR folder, Error description should be logged to error log text file and next file needs to be picked up for loading.
I don't think increasing max error count is an option as I don't know how many no. of input files are available as it depends upon the feed.
What is the best way to go.
Hello 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
I'm experiencing some problem with the affinity mask and affinity I/O mask configuration.
We are running SQL Server Enterprise Edition 2005 64bit on Windows 2003 Server 64bit with 4 dual core CPU. The actuelly configuration is:
Affinity Mask Affinity I/O Mask
CPU0 0 0
CPU1 0 0
CPU2 1 1
CPU3 1 1
CPU4 1 1
CPU5 1 1
CPU6 1 1
CPU7 1 1
We did it so, because we are running LiteSpeed Tool for the backup. LiteSpeed is configured to use CPU0 and CPU1.
Now, we know that the settings are wrong. If Affinity mask is 1 affinity I/O mask should be 0 and vice-versa.
My question is: how many CPU should I enable for affinity mask and how many for affinity I/O mask. Which are the correct criterias.
Thx in advence.
Laurent
Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
Hi 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
I'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
I 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?
I loop through a list of files in a directory and one of the tasks in the container is to validate the xml file against dtd if this succeeds i process the file and the lforeachloop gets the next file. If validation fails i then delete that file,the problem i am having is that whe the xml task fails it stops the whole for each container even though the failparentonfailure is set to false.
How can i get the foreach container to continue to the next file after the xmltask fails (validation) and the file is succesfully deleted?
PS is their away of posting the DTSx screenshot as this might explain it better?
My 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.
I am trying to use the Foreach File Enumerator and map it to a user variable.
I select a folder in the colletion.
I use *.* for file name.
I left the default value for retrive file name.
I assigned it to my user variable ( data type string)
I am get the following error "The element cannot be found in a collection"
I am sure that I have at lest 10 file in the folder.
Can anyone tell me why I get the error?
Hi,
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
I 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.
I 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
I 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..
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
I set up a basic ForEach enumerator loop and specified files of type *.sql.
In the directory, I had some files I needed to keep but didn't want the package to touch, so I changed the extension to *.sqlo. Much to my dismay, the ForEach loop picked up those files. (Though it did skip the *.xml and *.bat and a few other types...)
Sounds like a bug to me.
Hi Guys,
I'm importing logs from multiple servers from CSV format into a table. Inside the loop the 1st thing I do is insert the FileName into a table to ensure that I never process the same file twice.
The problem is that multiple servers produce the same filename, so if server A produces Log20061110.csv - when I import the log from server B ( also called Log20061110.csv ) into the table, it of course runs into a problem.
If there was some way for me to get an MD5 Hash of the file as a variable that would be solve my problem.
Does anyone know how I could accomplish that?
Thanks
Warrick
Hi,
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
Hello
I have a question
How is foreach loop container - foreach ADO enumerator performace in SSIS package compares to use of cursors in stored procedures
Is there any articles comparing them
I understand a lot of factors can affect the performance, however what is expected performance for the foreach ADO enumerator loop for large dataset. What is Microsoft recommendation for that - recommended - not recommended (using large datasets - over million records)
Thank you
Arminr Bell
Hello,
I want to set up a Foreach loop container to loop through several flat files. I have a connection manager set up for individual flat files. The metadata for the files is all the same. They are fixed width files, and contain sixty five columns.
I didn't see a place in the container properties to configure the metadata of the files. How can a Foreach Loop Container 'know' the metadata of the flat files that I want to loop through?
Thank you for your help!
cdun2
I have a Foreeach loop container that is looking for all the files in a specified directory. For each file in the directory I need to open the file, extract the contents, and pass the contents as a variable for a stored procedure call.
I was able to loop thru the files in the directory and see each file and store the name to a variable.
I was able to use an XML task to open one file via a file connection that connects to a specified file and store the contents in a variable and then execute the SQL task using the variable.
What I am unable to figure out is how to put the two processes together and get the XML task to open the file by using the variable that contains the filename. If anyone could help I would really appreciate it.
Thanks!
GN