Hi all,
I have a flat file which the first row contains the certain info. about this file. I want to read the first line from this file to determine if continue to next step. what's the task/transformation can be used to do this?
thanks
Patrick
Trying to find the best way to vaildate email formats when importing records to result tables etc... the first seems to run faster, but it seems too easy, like something is getting missed.
Does anyone know if using:
WHERE email LIKE '%@%.%'
instead of using:
WHERE
( CHARINDEX(' ',LTRIM(RTRIM([email]))) = 0
AND LEFT(LTRIM([email]),1) <> '@'
AND RIGHT(RTRIM([email]),1) <> '.'
AND CHARINDEX('.',[email],CHARINDEX('@',[email])) - CHARINDEX('@',[email]) > 1
AND LEN(LTRIM(RTRIM([email]))) - LEN(REPLACE(LTRIM(RTRIM([email])),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([email])))) >= 3
AND (CHARINDEX('.@',[email]) = 0 AND CHARINDEX('..',[email]) = 0) )
is any better, or are they both pretty much bring back the same result sets?
Thanks for sharing your insight.
I need a help on the following situation ,
i have a set of 6 file which comes into my ftp folder , the name of the file would be same when a set arrives but with a different extension.
one of the validation i need to make is if i recieve the set of file then it process the package , if i have less then six file in it should move files to a failure location.
the challenge i have is i can have multiple sets of file at at given time in ftp location.
supppose the file i receive in first set would be 1A1.exa, 1A1. exb, 1A1. exc, 1A1.exd , 1A1. Exe , 1A1.Exf
the next set file would be 1A2.exa, 1A2. exb, 1A2. exc, 1A2.exd , 1A2. Exe , 1A2.Exf
please advise how can i achieve it , please advise
thank you
I am new to SSIS...
I have a very simple SSIS package that loads a text file into a table.
It works fine when I test it from Business Intelligence, but fails in PRODUCTION with the following error:
The file name "\????????.txt" specified in the connection was not valid.
Please help.
Hi,
I am quite new to SSIS (I was a DTS developer) and I have a specific requirement to validate all incoming data using regular expressions. For each row in my input file, all columns will need to be validated against an expression. We have approx 60 different input files (a combination of xml and text files) with column counts going from 10 up to 120.
Also each input file will contain a footer which will need to be validated for record counts.
I would like the solution to be as generic as possible as the requirements for each file are similar -the only difference being the column names and the expressions to check against. I would really rather not have a different data flow task for each input file type as there are so many.
Can anybody suggest the most efficient/reusable way to do this? What I was thinking of was this:
Split the input file into detail/footer
create a temporary table based on the input file type with CHECKconstraints (regular expressions) for each column
for each detail record load it into the temp table
redirect any failures to another destination e.g. sql error table
Any other ideas would be very helpfull
Many Thanks
Gaye
Hi,
I have migrated a DTS pakage into SSIS. The DTS package validates a Textfile Source File using an ActiveX Script task.
Could somebody tell me how to validate a FlatFile in SSIS. Based on whether the file exist or if exist then whether its empty or not, I have to execute a database proc.
It'll be very helpful if somebody can assist me in this.
Thanks in advance.
Neetu
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
We have the following scenario: We receive CSV files every month for which SSIS packages were built to process the data. The following problems occur from time to time:
1. The structure of the CSV file changed (e.g. column added or removed)
2. There were no footers in the data, but now footers started to appear
3. Date format changed (e.g. used to be mm/dd/yyyy, but became mm.dd.yyyy)
4. Number format changed (e.g. from 2000 to 2,000)
Currently we have person who manually opens each file, and using our "validation document" validates to ensure none of these or similar problems occur. We would like to move away from this manual process if possible. I understand that items 3. and 4. could be caught by loading data into a staging table with VARCHAR data types, and performing validation before moving it any further.
Item 2 is a bit questionable (meaning depending on the footer size SSIS load could fail or not).
Item 1, however, is a sure fail of the SSIS package that directly loads the data into a table.
Thus I feel the two possible options are:
1. Create a custom script that will run through the file, row by row, apply all the necessary validations and report an error or continue if all checks out
2. Use some 3rd party tool to validate the files (semi-manually) before kicking off the SSIS processing.
OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.
View 3 Replies View RelatedI have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:
Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
Thank you.
Hi all,
I am new in SSIS. Anyone know how to valify number of record that I load from csv file to SQL database table?
For example, the source file call product.csv and target table in database named DSS table name PRODUCT. I load data from flat file to table then I need verification if count between source and target not match send e-mail to me.
Thanks.
Grace
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies View RelatedI have a txt file with format as following
MailAddress:Kienpt@ifi.local
DomainName:ETH2K
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:
- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)
Can you help me?
Hi,
I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine).
I retrive the database values in .txt,XML format. Now i want to take in .bak format.
give the Suitable solution for this.
Subashini.G
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Backend: MSSQL 65
My datafiles are bcp out from Syabse 11.
Because the order of fields are different, I need to use format file with BCP.
I generated my fmt files by using MSSQL's bcp utility with the instructions from SQL Server books onLIne
(section: Using a Format File to Selectively Copy data)
I cannot bcp in with format file. I got
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 50
Since the datafile is small, I manually shift the order.
Then, I bcp it in without format file and it worked fine.
Now I got data in my SQL Server
I thought it may be the datafile. Though it's text file, it's generated by Sybase bcp utility. :-)
So, I bcp out the data to a new ASCII file. I change the ID to make those records different.
Then, I re-generate the format file.
Now, I have everything generated by MSSQL. I tried again and I got the same error.
This table contains 1 Blob field and 4 varchar(255) fields. Will this be a problem?
We have to solve this problem. When our applications go live, the datafiles will be huge and we cannot edit it manually.
Please advise
Thanks in advance.
Elizabeth Pickering
set @date = getdate()
set @month = convert (varchar[2], datepart(mm,@date))
gives me 2 instead of 02 for month of February
any clues ?
What are format files and how do they help us in Bulk Load????
I referred to many issues send to the forum but could not get sufficient information on this.
My main issue is to bulk insert from a .CSV file in hte following format
12,23,"blrnnr","Address",45677,"bangalore".............68 columns
After bulk insert I am getting the double qoutes also into the table.
Now my problem is, How do I remove these double qoutes?
I don't want to use replace() to remove these quotes!!!!!!
I have attached a Screen Shot to describe this issue with this mail
Any information is appretiated regarding this issue.
Thanking you
Naveen
I am trying to copy the data in excel file into a table using the bcpand this is the code that I have. However the bcp utility does notseem to create a format file, which I thought it should do. I amprobably going about this all wrong so any help would be useful.exec master..xp_cmdshell '(FOR %i IN ("E:WUTemp*") DO (bcp#ProspectImportTest in "%i" -fE:WUTempProspect.fmt)'bulk insert #ProspectImportTest from 'E:WUTemp*."' with (formatfile ='E:WUTempProspect.fmt')Thanks.KR
View 2 Replies View RelatedHi.I was wondering if anybody could piont me to a reference for the .bakfile format.I need to upload a huge amount of data from a unix machine into an MSSQLServer database and have tried lots of different things with mixedresults.I occurs to me that if I can generate a .bak file on the unix side,move it over to the MS side and 'restore' the database, that wouldprobably be the fastest possible method.Thanks,-jim
View 2 Replies View RelatedA format file provides a way to bulk copy data selectively from a datafile to an instance of SQL Server. This allows the transfer of data toa table when there is a mismatch between fields in the data file andcolumns in the table.I take it this assumes the number of fields in the data file willalways be constant. What if it is not?My table has two columns but my datafile may have 2 to 4 columns and Iwant to always select only the first two. Is there a way to set up theformat file to accomplish that?
View 3 Replies View Relateddo anyone know the format of the *.ldf file?
View 2 Replies View RelatedI have AdventureWorks installed. I am using SQL Server 2005.
I need to import some large tab delimited text files into SQL. From the research i did I thought that using BCO would be the best solution becausr I will have to import these files and export in the same format.
I am attempting to follow the example at:
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
B. Creating a non-XML format file for character data
In my command window at C: I enter:
bcp AdventureWorks.HumanResources.Department format nul -c -f Department-c.fmt -T
I was expecting to be prompted with questions about the source file and have a format file created. When i press enter nothing hallende. No errors. Nothing.
Can come one give a beginner the step by step on how to creat the format file and use it to import (and export would be nice too).
Using sql server
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
Im not sure about the best way to go about this?
I have a format file which is working but not correctly. It is, forsome reason, dropping the first line of the input .csv file. Theproblem is something with the second coulumn of data having quotes init. Any ideas? Below is some info.Format file (I use firstrow=2 in Bulk Import command:8.061 SQLCHAR 0 3000 ","" 1 Provider_Raw_ID Latin1_General_CI_AS2 SQLCHAR 0 3000 ""," 0 none_name Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS6 SQLCHAR 0 3000 "
" 4 NetworkComparedTo Latin1_General_CI_ASSample input file:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompare dTo1,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp12,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp13,"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp14,"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp15,"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp16,"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp17,"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp18,"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1Thanks a lot!!!Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.
For example : "1234",abc,"id"
I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.
when installing sql server error showing file format is not vaild
View 3 Replies View RelatedThe data file contains the answers given to questions asked during the course of an interview. This file contains data only for completed interviews. The file is ASCII based, and contains data in a card column format. This means that each record is spread over several rows in the file €“ called cards. Currently 39 cards make up one completed record, although this may change over time. There are 80 columns per row.
sample of data is as follows
record 1
0000301 108033135 ds880783 100325080327000451334779005133477900 1 001
000030251334779000 0 LISA E 002
0000303MCCARTHY est 11000000000000000000000000100 003
0000304DODGE GRCARA 2008 004
0000305Northgate Chrysler Dodge Jeep 005
00003068536 Colerain Ave Cincinnati 45251006
00003072914OHDEMOSS,MIKE 03-26-200811 007
00003081 1 120529101010 9 008
0000309 009
0000310 010
0000311 011
0000312 2 012
0000313 013
0000314 014
0000315120642 1 015
0000316 016
0000317 017
0000318 018
0000319 019
0000320 020
0000321 021
0000322 022
0000323 023
0000324 024
0000325 025
0000326 026
0000327 027
0000328 028
0000329 029
0000330 030
0000331 031
0000332 032
0000333 033
0000334 034
0000335 035
0000336 120557 036
00003373108629008110.000 North-gate Chrysler Dodge Jeep 037
0000338 1 3 038
0000339 039
Record 2
0000401 108033135 ds880609 300192080327000931868842133184587707 1 001
000040231868842131 0 MARK 002
0000403MURPHY cst 10010000000000000000000000100 003
0000404DODGE 2005 004
0000405Hebert's Town and Country Dodge Chrysle 005
00004061155 E Bert Kouns Shreveport 71105006
00004075628LA436 03-25-200811 007
00004083 1 120608 008
0000409 090909 009
0000410 4 010
0000411 011
0000412 2 012
0000413 013
0000414 014
0000415120759 1 015
0000416 016
0000417 017
0000418 018
0000419 019
0000420 020
0000421 021
0000422 022
0000423 023
0000424 024
0000425 025
0000426 026
0000427 027
0000428 028
0000429 029
0000430 030
0000431 031
0000432 032
0000433 033
0000434 034
0000435 035
0000436 120642 036
000043731086290081 09.000A-Bears Town and Country Chrysler Jeep 037
0000438 1 3 038
0000439 039
i need to convert this into a coma delimtited file ,file and i am failing whats need to be done to achieve it
its quite a new concept for me
please advise
Hi,
How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ...
09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111
09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18
09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18
Cheers
I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0.
I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done.
Any suggestions ?
Regards,
Stuart.
I added a field to an exisiting table (CHAR 30), and I added to field to the BCP Format File.
The BCP worked fine before the new field, and still works fine when I exclude the new field
from the format file, but with the new field I receive the following error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file
(2 row(s) affected)
Here is a copy of my format file:
7.0
11
1 SQLCHAR 0 6 "" 1 Empl_ser_no
2 SQLCHAR 0 2 "" 2 Empl_stat_cd
3 SQLCHAR 0 1 "" 3 Guest_type_fl
4 SQLCHAR 0 30 "" 4 Job_tl
5 SQLCHAR 0 14 "" 5 Pager_no
6 SQLCHAR 0 1 "" 6 Security_Clear_fl
7 SQLCHAR 0 9 "" 7 SSN
8 SQLCHAR 0 30 "" 8 Emer_cont_nm
9 SQLCHAR 0 14 "" 9 Emer_cont_no
10 SQLCHAR 0 1 "" 10 Citizen_fl
11 SQLCHAR 0 30 "
" 11 Job_Res
The input file is just a fixed positonal text file, and I have checkid it 100 times- it is fine.
Any sugestions from any BCP gurus out there? THANKS
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
Thanks!