Can DTS Import Varying File Names?

Oct 5, 2000

I receive several TXT files daily that need to update information in SQL Server databases. The process requires that all TXT files be appended to a master file and also update individual files' information based on the TXT file name. For example:

File TABLE1_x_ddmmyy.TXT (ddmmyy = date, x = "O" or "B") is to be appended to the master file and also update SQL table "TABLE1" by setting a flag for those records in the table that match a unique key that is provided in the TABLE1.TXT file.

In VFP, I had the following process in place:
a) open the TXT file.
b) read its file name and open the corresponding VFP file
c) update the VFP file based on the key provided in TXT
d) append the key to the master file.
e) repeat c-d for next record in TXT
f) repeat c-e for next TXT file

Using the same process with ADO takes a considerable time since I am processing one line at a time.

Is there any way to do this using a DTS package of some sort? How can I read the TXT file names in SQL Server?

Thank you.

View 3 Replies


ADVERTISEMENT

Import Text File Report With Varying Content And Records

Oct 23, 2014

I get error reports in simple text files like the one below in relatively the same format. The only thing that varies is the number error reasons as there can be any number of error reasons for a file. Usually there is only one but there can be a handful. What is the best way to capture the error description and count of errors no matter how many there are? I want to take these items and update a table I have in sql server 2008r.

Report Message example:
************************************************** *****

Original File Name: some.file.YYYYMMDD.d.incr.02of02.1.dat
Source File ID: file02YYYYMMDD
File Receipt Date: 10/17/2014
Total records received: 1331136
Total records loaded: 1329987

ERROR REASONS
Error code: EBBW002 Error desc: Duplicate Record Total records: 1146
Error code: EABC001 Error desc: Invalid Length Record Total records: 1
Error code: ERRCM10 Error desc: Missing First Name Total records: 2

Total number of Errors encountered during the ODS update processing: 1149
************************************************** *****

View 2 Replies View Related

How Do I Import A Varying Column Width Flat File Into A Table Using SSIS?

Apr 24, 2008

How do i import a Varying Column Width Flat file into a Table using SSIS?


I have a flat file that has 4 columns with varying width
Like I should read the file as
Col 1 - (1 to 10 Characters)
Col 2 - (12 to 21 Characters)
Col 3 - (22 to 35 Characters)
Col 4 - (36 to 38 Characters)
At the end of the record is a "LF"


I think "Fixed Width" Columns allow me to define a standard column length for all the columns.. Right?

Any thoughts on how to?

View 9 Replies View Related

.dbf File Import (duplicate Field Names)

Mar 27, 2006

I am importing a file creating by an application which exports the file into .dbf format.  Very unfortunately, this .dbf file can have fields with IDENTICAL column_names.  Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver.  However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.

Can anyone make recommendations here that might help?

Oh, this is SQL200 in case that impacts what you might advise!!!!

View 2 Replies View Related

Import Excel With Varying Worksheet Name Using DTS

Jun 6, 2007

Hi Everyone,



I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.



Thanks,

Eric

View 1 Replies View Related

T-SQL (SS2K8) :: Import To Table From Varying Tab Delimited Text Files

Feb 10, 2014

I need to import data to a MSSql table from massive (read: a million and a half rows, every single day) logs that come in .txt format separated in tabs with a ";" symbol and then have some stored procedures analyze that data to generate some reports in an excel file with that info. The text files include the column headers in the first row and the data starts on the second one.

The challenge is that the text files differ in column order and count every single day.

The analysis that I need to do only needs about 15 columns from the nearly 90-120 that those files include, and those columns sadly happen to be in a different order in those files.

View 8 Replies View Related

Help Importing File With Varying Column Widths

Nov 7, 2007

True story. Thanks. Dont think that fixed or ragged is going to work for me though. File does not seem to be structured.


I have what appears to be a fixed length file that I would like to import using SSIS. However it does not look loike the spacing between fields is constant. Is there a way to say from character 1 to 10 is X, and 11 to 15 is Y, etc etc? Below is a couple of rows that all pertain to the same record.

01000000225672101242253 55232101242253 5TKTT / 0101 FFVV 5235MCYSNR/CE MUSSETT/BRIAN.A.MR 20071017U7700002277000022 U00000000ZAJNB JNB ZA 00000000 00000000 00000000 SITII Y06 405
02000000235672101242253 55232101242253 5ICE 20071017 1407000017OCT 17OCT
03000000245672101242253 55232101242253 5 PLZPLZ ZAR 1000 ZAR 72ZAZAR 140ZVZAR 562XTZAR 1774 ADT PENALTY APPLIES
04000000255672101242253 55232101242253 50000017740000000100000ZAR200000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 000000000000000000000000000000000ZA 00000007200ZV 00000014000EV 00000001600YR 00000054600 00000000000 00000000000 00000000000 00000000000 00000000000
05000000265672101242253 55232101242253 51 OPLZ DUR CE CE 0633 0633 K K 18OCT18OCT18OCT1625 1740 OK KOW 2 DUR PLZ CE CE 0602 0602 B B 20OCT20OCT20OCT1000 1115 OK BOW
07000000275672101242253 55232101242253 5CCIK5221 000001774005221591015192982 0308M067781 00000000000 00000000000 00000000000
08000000285672101242253 55232101242253 509PLZ CE DUR550CE PLZ450ZAR1000 END CE XT16EV546YR 1 2 3 4

What do you reckon is the best option? SUBSTRING in a Dervied Column? Below is an example of the text.

View 5 Replies View Related

Integration Services :: Import Varying Number Of Tables Each Time From One Database To Different Database

Sep 9, 2015

I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.

I have performed the following tasks **

1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)

3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.

4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.

What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.

View 5 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

DTS Import Renames My SPs To Previous Names

Jul 20, 2005

I am using SQL Server 7 SP4.I have created a blank database in which i am trying to import using DTSwizard all tables/views/stored procedures without any DATA (records).I keep getting different errors when importing the views and/or the SPs.I've tried many things unsuccessfully.Now even after the error msg of the DTS, i see some of the SPs undertheir previous names.In other words, let's say i have an SP called zprocDeleteProduct andthis SP was previously called procDeleteProduct.When i check the 2nd database, i see the SP as zprocDeleteProductinstead of seeing it as procDeleteProduct.I have other SPs that are being imported with their previous names.I have renamed these SPs from within Access XP. I don't create SPsin SQL EM or SQL Query Analyzer.How can i fix this problem? I don't want to import SPs under their previousnames.Thank you

View 3 Replies View Related

Integration Services :: Move Multiple Files Based On File Names Listed In A Spreadsheet / File?

May 27, 2015

I need to move specific files from a server to another server on a monthly basis.  There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server.  I would like to easily add or delete the file list as needed.  I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them.  With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable.  Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....

View 10 Replies View Related

How To: Variable File Names When Exporting A Report To A File Share

Jan 19, 2007

I'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.

Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.

Can anybody tell me how to accomplish this.

View 1 Replies View Related

Integration Services :: Data Import From Excel Multiple Sheets With A Pattern Of Sheet Names

Aug 20, 2015

I have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!). 

Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task. 

View 5 Replies View Related

Error Regarding File Import Through Import Wizard

Jan 12, 2006

Hi all,

when trying to ímport files to our database server from a client, I keep getting an error:

- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1).
 (SQL Server Import and Export Wizard)
 
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175).
 (SQL Server Import and Export Wizard)

... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.

 

I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.

best regards

 

Musa Rusid

View 1 Replies View Related

.mdf And .LDF File Names

Jan 2, 2008

Hi All,

I am using query analyzer to execute my sqls .

I want to take out .mdf and .LDF files and put it in another machine .
But there is no .mdf or .LDF file with prefix as my database name . I think the file name might be something else .
Please help me in finding out the .mdf and .LDF of a particular database .

Thanks ,
Sushi

View 6 Replies View Related

Get File Names - How To Do This

Jan 2, 2008

Hi,

Every day I ftp a zip file. Inside the zip file are 4 text files. Every day the names of the 4 files are different, so I can't know beforehand what the names are.

I am wondering what is the best (most efficient) way for me to get the file names inside variables? Should I use a script task? Or is there another way to do this?

Thanks

View 3 Replies View Related

How To Search File Names? Help!

May 29, 2002

Hi ALL!
Our application needs to search for exact match of file names (which will be 70 characters in length) in a table which has thousands of file names in its file_name field. What is the best way to this? Shall we index the table on file_name column? Or any othe way, as the application will do this search several times all day. Any ideas are appreciated.
Thanks.
KSN.

View 1 Replies View Related

Logical File Names

Apr 24, 2002

Hi,
Does anyone know how to change the logical file name of a database?

Appreciate any help.

Steve

View 1 Replies View Related

Backup File Names Truncating

Apr 18, 2002

I'm performing a full backup of a database, and the file name that I am creating for the backup is truncating. This keeps happening and can't figure out why.

I have a stored proc that I run:

CREATE PROC usp_Backup_old_srvra_to_srvrb

AS

DECLARE @DOW varchar(10),
@Filelocation varchar(50)


--Get Day of Week and set backup file name
SET @DOW = DATENAME(dw,GETDATE())
SET @FileLocation = 'SERVERAE$MSSQL7BACKUP'+ @DOW + '__old_BKUP_SRVR_0418_0800.bak'

BACKUP DATABASE TEST
TO DISK = @FileLocation
WITH INIT,
STATS
RETURN (0)


The backup file that was created ended up being:

THURSDAY__old_BKUP_SR

Any idea why it didn't pick up the whole file name that I wanted?

View 2 Replies View Related

File Names With Rowcounts Using DOS Command

Mar 26, 2015

I have below requirement and it needs to be addressed using sql.

There are 18 text files(file names are dynamic) and each file having different row counts in some “xyz” folder.

Here, I need to generate 19th text file which has the list of file names with row counts.

I am planning to import all of the text files into database and trying to generate 19th one.

View 1 Replies View Related

Renaming Logical File Names

Jul 23, 2005

Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?

View 1 Replies View Related

Working With Dynamic File Names

Jul 28, 2006

I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.

I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.

thanks,

Scott

View 3 Replies View Related

Restore Database To Different File Names

May 9, 2008

Hi SQL server experts,
I'm trying to restore from a backup file.
I need to restore the datafile and logfile to different names.
I modified the options page, changed the 'Restored As' file name.
I clicked on the General page and Clicked on the Options page again, the old values came back.
How do I handle this?
Thanks in advance.
Pingx

View 3 Replies View Related

Restore SQL DB With Correct Logical File Names

Feb 20, 2007

Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance

View 14 Replies View Related

Save Input Flat File Names.

Feb 19, 2008

Background:


I am new to SSIS.

I have a data flow task within a For Each Loop Container.

Its reading a Demilited Flat File and inserting into a DB table.
The Flat File Source reads a specified folder and picks up all files with extension .txt

Question:

How can I record and save the file names the package picks up and loads it in ?

For eg:
If I have under C:Test folder
File1.txt
File2.txt
File3.txt

And the package picks up all the files above and loads them in, is it possible to read the file name its processing ?
I have a need to read these file names ( in this example File1,File2,File3 ) and store them in a DB table.

Appreciate your help.
Thanks in advance.
AkB

View 4 Replies View Related

SSIS Runs Out Of Temp File Names

Apr 24, 2007

Hi,



A collegue of mine is having some issues. I hope someone can help
















Hi all,

This is my first call for technical assistance so go easy on me.

I'm having a problem in one of my SSIS packages. In brief, the process involves copying the rows from an untyped table to a typed table. There are about 45,000 rows in this table and during the copy ("OLE DB source" to "OLE DB destination") the process appears to hang for about 5 minutes eventually crashing with a "The buffer manager could not get a temporary file name. The call to GetTempFileName failed."

After several attempts using all the trace info I could master this is the order of events with some interesting numbers and facts:

1. The SSIS package goes swifty through "Validation", "Prepare for Execution" and "Execution" in less than 1 second, with "Execute phase is beginning" being the last message on the "Progress tab".

2. Using Performance counters I note that in the next 5 mins the values for "BLOB bytes read" slowly rises and then after a couple of mins so does ""BLOB files in use" the latter reaching a figure of 65534

3. When this figure is reached, SSIS starts creating thousands of zero-size files with the name DTS####.tmp (where #### is hex e.g. DTSB4C1.TMP) in the TEMP folder (C:Documents and Settings<username>Local SettingsTemp in my case).

4. When I started running this package there were 130 files in my TEMP folder; As soon as the combined total of files in TEMP reaches 65664 (i.e. 65534+130), SSIS starts producing the errors list which includes the one I listed above and eventually it clears the TEMP folder down to the original 130 files.

5. My conclusion (thus far) is that SSIS creates all these 1000s of tmp files but in my case hits some kind of maximum (either a folder limit or runs out of hex combinations for the file names) and then crashes.

6. The only thread I found on the internet suggested setting up an environment variable "BLOBTempStoragePath" and assigning a value of "C:Temp1;C:Temp2;C:Temp3;C:Temp4" so that SSIS can span across a number of "temporary" folders instead of the 1 default folder contained in the "TEMP" environment variable.

7. Setting the above environment variable in Windows 2000 did not work for me (tried it as both a user variable and a system environment variable). So here are the facts so far - ANY assistance will be hugely appreciated - I have no idea why all these temporary files are being generated - I have created SSIS packages handling data sets 10 times bigger than this one without these problems so I don't think it's size related.

View 3 Replies View Related

SQL Server 2008 :: Renaming Logical File Names

Apr 30, 2015

Is there any danger with renaming the LOGICAL file names behind the database?

There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.

For instance:

database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf

Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].

View 3 Replies View Related

Use Of Brackets (parenthesis) In File / Folder Names In Windows

Mar 19, 2008

Hi,
On x64 versions of Windows server 2003, there is a folder called "Program Files (x86)". The use of parenthesis in this name has caused a few issues so far, particularly with connecting SQL Server 2005 to Oracle using SSIS. After several hours of searching I realized that the parenthesis was causing it, and eventually I found that the best way is to avoid creating such a name while installing the OS itself, by using winnt.sif. This would be better than renaming it and changing registry entries after the installation is done.

I realize this may not be the best question to ask of Microsoft, but why not just call it "Program Filesx86" by default so no special procedure has to be done to get around these issues? The use of parenthesis is likely to cause even more compatibility issues with other software later?

BTW I use Microsoft products regularly and love most of them

View 8 Replies View Related

Flat Files Having Only Column Names In One File And The Rows In The Other

Jun 11, 2007

Sorry if this question had already been answered previously. I was unable search the forum on this topic. How will I merge these and then configure the first row as Column names (As this helps to map to the destination column names automatically)

View 3 Replies View Related

Varying Parameter Size..?

Nov 6, 2006

Hello,

I want to write a function that takes varying number of parameters.I mean:

Fonk(4,5,7)

Fonk(2)

Fonk(6,6,6,8)

There is a way to do this in T-SQL?

Thanks in advance...

View 5 Replies View Related

Bestpractice For Varying Outputformat

Nov 28, 2006

Hello

I need an advice for an easy maintainable and highly flexible solution using SSIS. We're supplying our customers with an exportservice. The data is extracted from different tables, all with the same tableschema, fairly easy to create a foreach-container and iterate through the tablenames and extract data and use a flatfile destination to write it to...

But the not so easy part is to have different exportformats and still be using only one package. I could maybe use different ConnectionManagers for the FlatFileDestination, but that's not a very easy maintainable solution in my world. Our Customers will soon demand a webinterface where they can select necessary columns and apply different formatting for example they would demand the datetime to be '2006-12-24' or maybe '12/24/2006' etc. for alot of the available columns.

Any good suggestion on how to accomplish such a task?

Kind regards

View 1 Replies View Related

SQL Server 2008 :: Logical And Physical File Names Of All Databases

Apr 3, 2011

Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?

View 5 Replies View Related

Dynamic File Names For Raw Files Created By Conditional Expressions

Feb 26, 2007

Hello,

I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.

My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible?

Regards

RH




View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved