.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
 
I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance


View Complete Post


More Related Resource Links

Saving SSIS results to Log or text file using dtexec

  
Hi, How to save the SSIS package results to log file using dtexec command............please help regaridng this...........   Thanks in advance,

Calculated Column: Text ==> Date and Time Column (Date+Time)

  

 

Hello,

 

I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)

 

How can I have a calculated column which is a Date+Time based on a text_column

Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time

 

Example:

20070629_0848 converting to 29/06/2007 08:48

 

==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)

 

Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...

 

It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....

 

thx

Frederik

 


SSIS audit column Get date and total count of row

  

HI

I have one task in which i have to transfer data from sql to flat file  also i need one more flat file in which i have Number of Row and Getdate

I use multicast column  and then agreegate to count row  i do not know How to achive getdate in this.

 

 


Importing text file datetime string into SQL datetime column

  

Hi,

Using SSIS 2008 R2 to import text files.  Two of the fields contained are datetime values in the format "2010-09-24 18:58:29.220".  Ultimately both the date and time need to be extracted with precision to the second, at the very least.  When I import the text file, however, the time value seems to get truncated before the date value's being imported, for an imported result of "2010-09-24 00:00:00.000".

Is my problem with the Flat File Connection Manager?  Regardless of which DataType choice I use for the properties of my datetime values--I've tried every form of date datatype--the imported column values contain the correct date but the wrong time value.

Is there an easy way to import a text file datetime value straight into a datetime column?  Is it possible, or am I trying to shortcut what should be a proper process?

Thanks,
Eric


Importing text file datetime string into SQL datetime column

  

Hello,

I'm importing a text file into SQL Server 2008 R2 using SSIS 2008 R2.  Included in the text file is a datetime column with values like "2010-09-24 18:58:29.220".  However, when I import the file, the stored value has the time portion replaced with what I assume is the default, such as "2010-09-24 00:00:00.000".

Is there an easy way to import a datetime value from a text file into a datetime column in my table?  I've tried every form of date datatype in the Flat File Connection Manager, but I still keep getting the correct date portion with the incorrect time portion.

Am I trying to do something that wasn't intended?  I see Todd McDermid's page (http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html) and am starting to wonder if perhaps I'm trying to shortcut something that I shouldn't.

Thanks,
Eric


SSIS - load table from source text file with multiple record lengths

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Load multiple record length text file into table using SSIS

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

SSIS - Derived Column Error Rows will not redirect to table

  

I am new to SSIS and I have created a package that generally successfully takes a Flat File Source, runs it through a Derived Column transformation and then pushes the data into a SQL Server Database.

I have a Derived Column Error Output set up, but it is not working.  If the data has an error the entire package fails, not just the specific row.  I have tested the package to load successfully and then manually gone into the flat file and changed the data to purposely create an error in one row. Having cleared the load table I rerun the package and it fails entirely rather than redirecting the one bad row.

In the Derived Column Transformation Editor I set the Error and Truncation values to Redirect Row for all the columns.

In SQL Destination Editor for the bad rows I am only mapping the ErrorCode abnd ErrorColumn values to the destination table fields of the same name.  Could this be the problem? Should I map all the fields?  I would like to map all the source (derived) fields into a single text field in the destination table if possible.

The Advanced settings have only the Table Lock and Check constraints boxes checked.  Timeout is 30.

I am not really sure how to set the Advanced Editor properties.  I have left them at the default values.  Any suggestions here?

Thanks.

Steven

Converting a portion (numbers) of a text file name into a date type

  

I am working with a number of Flat Files that will be processed daily. The FileName is a combination of text and numbers. The numbers are the date of transaction. Therefore, I need to pick up the numbers and use it as a Date (DateAdded) inserted into the database. However, the positioning of the numbers is not fixed.  

Examples of what the File Names:   Dial20100610.txt and Dialog20100611.txt

Now, I have inserted all the data into a staging table called Raw_Staging with File Name as the name of the column (eg. Dial20100610.txt, Dialog20100612.txt)

Is there a way to strip the text character from the columns with tsql so I am only left with the numbers and then convert it to a Datetime datatype so I can insert it into the destination table?

Upload Text File data to SQL table Using SSIS

  
I have a text File with following format  (  HED - Header Row, CL-Data (Sample 3 Rows) , TRL-Trailer Row)


HED201008240001PCMCL


CL 012352908607309000014090 2010-03-04 16:12:53.077000000 Inserted / Log ICode Action on Order Number / 33949591 / with / I-Code / IDEC/ / Memo: / cci questions about alert/


CL 012352958107309000020378 2010-04-14 10:20:03.340000000 Inserted / Log ICode Action on Order Number / 36992594 / with / I-Code / ISRB,INEW,ILKD/ / Memo: / cci to cxl, saved with benefits and gave her instructions to move account online/


CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Inserted / Log Customer Info Change Action CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Updated / Log Customer Info Change Action


TRL201008240001PCMCL 0000007349



I have to Import The above Mentioned data in to Following Table



CREATE TABLE [dbo].[Log]

[Type] [varchar

How to sort a TEXT Date Column ?

  

Hi,
I have data :- 31-May-07

Its in a calculated column of the list. The method I used to get it :- =IF(FDate="","",TEXT(FDate,"dd-mmm-yy")) {FDate is a DateTime type of column. The returned value is stored in a "Single Line of Text" column type.}

I am now unable to sort that column as its Text now and sorting is done on basis of strings.

I need to sort this column on basis of Date.

Any help ?

Thanks!!


SSIS text qualifier results in text file containing "_x003C_none_x003E"

  

There is a bug in SSIS when exporting data from SQL to a comma delimited text file where you specified no text qualifier.

This bug apparently only occurs when you develop the SSIS on a x64 win7 PC and copy the .dtsx file (windows explorer copy/paste) to network path of a x86 SQL server and schedule the job to run from SQL Agent on the same x86 SQL server.

When the SSIS runs, the text file is written out containing text qualifier = "“_x003C_none_x003E".

If you look at " _x003C_none_x003E", it actually means <none>.  x003C = "<" and x003E = ">".

 

If you go into the SSIS package, double-click in the connection manager section to open the flat file connection manager editor and try to clear the text qualifier removing the <none>, the <none> value get added back in.

SFTP is converting text file format, now SSIS sees an empty file

  

Using BIDS that came with SQL Server 2008 R2, I created a text file import package.  I used files generated from my local machine for local testing.  However, I find that these files get their format changed after being SFTP'd to the data warehouse.  Not being able to visually see a problem with the text file to be imported, I copied it down to my local machine and compared to one generated at my machine.  Not until I chose "Show All Characters" in Notepad++ did I see the difference: my local file ends its lines with a carriage return-linefeed character, whereas the file that went through SFTP ends its lines with a linefeed character only.

I suspect there's something very basic going on here that I don't know.  I won't ask anybody to explain it, but I'm wondering if there's some way I can import both file formats.  The column numbers and lengths are identical, it's the just line ending character that's different and causing zero rows to get imported.  The only other thing I know to do is recreate my flat file connection, and I haven't even tried that yet.

Thanks,
Eric


Problem in Date Format While Exporting To Excel Destination From Flat File Source In SSIS

  

Hi,

I have a flat file(.csv) which contains data(strings incuding date in yyyy-mm-dd format).This i am using in Flat File Source and want to export those data to an Excel Destination.Before exporting to Excel Destination i have created a Derived Column component where i am using an express to get day,month,year part and creating a date sting in dd/mm/yyyy format then conveting it to DT_DATE datatype and then exporting to Excel Component.But while exporting to Excel this date format is getting changed to mm/dd/yyyy format.

The expression i used in derived column is

(DT_DATE)(SUBSTRING(date,9,2) + "/" + SUBSTRING(date,6,2) + "/" + SUBSTRING(date,1,4))

where date is a column from Flat File Source.

Even I changed the Locale to English(United Kingdom) in Flat File Connection Manager for the above .csv file.But still it didnt work.

I searched a lot in google and failed to get any solution on this.

Can anyone help me in solving this issue ?


Unique text value in databse column

  

Hi Everyone!

I know this is probably a very basic question but how do I keep a column's text values unique in SQL server (ie for a unique username)?  The column is not an integer it's varchar(50), and it's not the primary key but I could make it the primary key if recommended.

The front end could handle it;  where the user would enter text, it's validated, then a query is done to see if that username is available.  If so, then user can decide to choose it and an insert is done, if not they pick a new username and process repeats.  With this approach, the responsibility of keeping the value unique solely rests on the front end, nothing on the database side.

Is this the correct way to handle this? 

Thank you!

 


Writing to a text file

  

Hello,

I have the following  subroutine in an asp.net application class. The subroutine writes data to a text file upon application login.   My question is that if this function was called at the same time by two different  users would it cause any kind of error. Is there a need for a try catch?

 Public Shared Sub writeToLogFile(ByVal UserName  As String)
        Dim strLogMessage As String = String.Empty
        Dim strLogFile As String = System.Web.HttpContext.Current.Server.MapPath("~/Log.txt") Dim swLog As StreamWriter
        strLogMessage = DateTime.Now.ToShortDateString().ToString()  " ==> " & UserName

        If Not File.Exists(strLogFile) Then
            swLog = New StreamWriter(strLogFile)
        Else
            swLog = File.AppendText(strLogFile)
        End If


        swLog.WriteLine(strLogMessage)
      &nbs

The file reached the maximum download limit. Check that the full text of the document can be meaning

  

Hi,

I'm facing an issue with the indexing.

I have 1 WFE+Index server+DB server.

Index server is not installed with MS FIlter pack 1.0

When crawling, the there will be document with warning in crawl log:
The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

Documents that with warning are such as doc, ppt, xls, docs, ppts and many others.
However, I view into the successful crawled document, there are doucments with ext doc and ppt.

For large file index, there are MaxGrowthFactor + MaxDownLoadSize required to be added into the index server.


As my understanding is, MS Filter Pack should installed into index server(already did, correct me if i'm wrong).

I looked into the Office SharePoint Seach(CA>Services in farm), if the server is appointed to "Use this server as indexing server", then MS Filter Pack is suppose to be installed into that particular server as well.

At the bottom, there also has another option is "Use all web front end for crawling".

The question here is, IF the option "Use all web front end for crawling" is selected.
Does the WED FRONT END Server required to installed the Ms Filte

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend