September 10, 2013 at 11:18 am
There is a text file placed on a URL lets say http://mysubsite.domain.com/Customers.txt
This text file has \t \Column1 \t \Coilumn2 and so on then to have new line data for column1 and then Column2 with n rows
I have to pull this list of customers in existing customer table. if this customer already exists then do nothing if this customer is not in list then add in Customer table. I need some guidance if this could be done through SSIS, if yes then how? I have no idea how to implement this in SSIS. I was trying to write a win32 service in c# to download text file locally and then run BCP to export data in a temp file then call SP to see if the customer is updated then update else if not found then add in Customer table. I am not sure if this could be done through SSIS and if yes i hope it would be fast and quick to implement, if someone already worked on it. What is the best option to choose, I need to know what is the best solution and how it could be done if possible in SSIS? I need to do this on daily bases to get text file up to date with my Customer table.
Shamshad Ali
September 10, 2013 at 11:56 am
Shamshad,
I am leaving it to you how to run an executable in SSIS package. What you need to run is WGET for Windows. I used that in the past, works well.
Please navigate to
http://gnuwin32.sourceforge.net/packages/wget.htm
Wget for Windows
for the download files. Download, install and get your file from any http and ftp site. It can work as a batch and recursively.
Yelena
Regards,Yelena Varsha
September 10, 2013 at 12:21 pm
I do not understand this, How this will help me in SSIS, if some one has a complete solution, please share, if there is any better suggestion available, please share in such a way that I can understand it. I am new in SSIS development and have on muchh idea how can he help ne..
Shamshad Ali
September 10, 2013 at 12:37 pm
Hi,
You can still follow the link, istall wget and test it interactively, without SSIS to download a file from the web. Then you can use a simple batch file .bat to test the download in batch mode. when this works, then run this batch file in SSIS by using Execute Process Task, read more on this link
http://technet.microsoft.com/en-us/library/ms141166.aspx
Yelena
Regards,Yelena Varsha
September 10, 2013 at 12:52 pm
I would use the HTTP Connection Manager to import the file into a staging table.
http://technet.microsoft.com/en-us/library/ms137602.aspx
After this I would write a TSQL statement using the MERGE statement and either call the MERGE statement from a Execute SQL task within the SSIS package or (I think preferably) call the MERGE statement outside the package to update the permanent table from the staging table.
September 13, 2013 at 11:09 pm
Hi all,
Thanks for help. I got success to download file locally using script task, but when I try to import this text file data through BULK insert script or through Import/Export wizard, I am facing following error:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 9 ("Standard_Order").
--- error in SSIS:
Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column ""Street_Address"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "output column ""Street_Address"" (14)" failed because truncation occurred, and the truncation row disposition on "output column ""Street_Address"" (14)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Shamshad\Development\SSIS\Sample.txt" on data row 9.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sample_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
I have attached two files the sample.txt is one that is imported/downloaded through SSIS. which is giving above error - this is because the line terminator seems missing, however if i go to site and copy and paste the text which you can see in sample1.txt, the BULK statement successfully import data in SQL Server table. I used following script to import:
Sample.txt gives error
Sample1.txt import data in tblPeople.
BULK INSERT tblPeople
FROM 'C:\Shamshad\Development\SSIS\Sample.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR='""',
ROWTERMINATOR = '');
truncate table tblPeople
The link I am using is http://www.bis.doc.gov/dpl/dpl.txt
Please help
September 23, 2013 at 12:25 pm
did not get reply since a week now... please see my last questions in continuation with my original question...
September 23, 2013 at 1:21 pm
Ah, Ah - sorry was very busy but now read your description. Looks like your file has been created on UNIX or LINUX.
In Windows the row terminator in CR + LF or Chr(13)&Chr(10) in characters.
In UNIX and LINUX it is just LIne Feed (LF) without Carriage Return (CR) so when you copy the text to Notepad and import the resulting file it aquires Windows format. Same as in LINUX, for example I use unix2dos utility to convert files.
In terms of BCP there is a advice by Razvan from this link:
"...Therefore you should save the format file using BCP, then edit the format file manually and replace \r with a and then use the modified format file (specifying the -f parameter) to import the CSV file...."
If you are not comfortable with BCP format files, then pre-treat the file itself converting the UNIX format to Windows format. Google UNIX2DOS utility for Windows and there are free downloads for this utility.
If this does not help, then read the characters at the end of the lines in your file with any language you know, like VBScript and return ASCII code and a Character for these hidden characters.
I use VBScript for this.
This is the basic script to read hidden characters in Test.txt file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Temp\Test.txt", 1)
Do Until objFile.AtEndOfStream
strCharacters = objFile.Read(1)
Wscript.Echo strCharacters & " " & Cstr(asc(strCharacters))
Loop
Yelena
Regards,Yelena Varsha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply