SSis package import data from flat file to sql server table using sql server 2008 and c#.

Saroj13
Saroj13 used Ask the Experts™
on
Hi,

I am using Data flow task. In this task, I am using flat file source task to import the data to sql server destination. Data is succesfully import in to sql server tables.

Question to be done: I am having SqlRawTable. This table is having RawData field. I need to append the following text in all the records in RawData field while importing the data from flat file to sql server destination.

text to be added
1.  '10.23.12.345 currentDateTime! . It should be added before the beginning of the record.
2 ' . This should be added at the end of record in sql column.

How I can accomplish this using SSIS Package importing the data from flat file task to sql server destination task.

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. BI  Developer
Commented:
If I understand correctly, Use a "derived column" and whatever you need using an expression a

Author

Commented:
I need to add
“Oct 31 07:50:02 10.33.28.137 usage: 17.0.0.1!” in
the sql column RawData1

RawDataColumnValue   textChangeATMCredit

I need to add 'Current DateTime 10.33.28.137 usage: 17.0.0.1! + RawDataColumnValue + '

How can i do this with derived column
Jason YousefSr. BI  Developer

Commented:
that should do, add your value in front of the 2 columns.

if you need to add a space between the 2 columns you can add      + " " +     between the 2 columns (RawDataColumnValue  and  textChangeATMCredit)
"Current DateTime 10.33.28.137 usage: 17.0.0.1!" + RawDataColumnValue + textChangeATMCredit

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Importing the data from flat file to sql server create one RawData Column. This column is in text format.Around 8000 records are present in below format.
10.15.115.207 - - [14/Jun/2011:00:00:43 -0600] .....

Now, I need to append the text in the RawData Column.
text to be added is currentdatetime. How to get the current dateand time?

Appended text is 'CurrentDatetimevalue + 10.33.28.137 usage: 17.0.0.1!' + RawDateColumn + '

How i can accomplish this. I just have one column only. No 2 columns

Author

Commented:
here is my expression:
Its giving me the error:

"'" + " " +  (DT_WSTR,4) @[System::CreationDate]  + " " +  "10.33.28.137 usage: 17.0.0.1!" + " " + RawDataColumnValue + " " +  "'"


RawDataColumnValue  is the log string thats present in the Raw Data column of sql table. I need to append in the beginning of the record date and text

Author

Commented:
one or both operands need to be explicitly cast with a
cast ... The data types "DT_WSTR" and "DT_TEXT" are incompatible  error
Jason YousefSr. BI  Developer

Commented:
I thought you have 2 columns !   try that..

(DT_WSTR, 30 )GETDATE() + " 10.33.28.137 usage: 17.0.0.1!  " + RawDataColumnValue

Open in new window

Jason YousefSr. BI  Developer

Commented:
wait a minute!! if you're using TEXT , derived column won't work !

derived column will truncate over 4000 chars !!  even if you configured flat file source with string of 80000 !!!

you'll need to split (substring) and add that values...then merge again, on a key !

Author

Commented:
one or both operands need to be explicitly cast with a
cast ... The data types "DT_WSTR" and "DT_TEXT" are incompatible  error
This error is still coming

RawDataColumnValue is DT_Text format

Author

Commented:
How I can  use split and merge?
Please help me in this regard. I am new...
Jason YousefSr. BI  Developer

Commented:
how big is the column you're loading anyway? why do you need to select data type as TEXT?

Author

Commented:
Column may have 12,000 characters for each record
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
You can use Derived columns and add column in between the Data transfer from Source to Destination.
Capture.PNG
Jason YousefSr. BI  Developer

Commented:

You'll need Convert Text Stream to String within a script component , will test it and post back..

Jason YousefSr. BI  Developer

Commented:
Ok try to follow these screen shots,

1-add a script component
2-select your column
3-create an output column
4-edit the script and add your desired text
5-output before and after....  date + 10.33.28.137 usage: 17.0.0.1
5-use the newly created column instead of the old one in the flow.
1.jpg
2.jpg
3.jpg
4.jpg
5.jpg
You could set up the table into which you are importing the data so that it has a column for the current data (defaulting to GETDATE()), any additional leading columns (defaulting to the appropriate values), a second column (let's call it TheData defined as CarChar(MAX)), and a final column that defaults to whatever you need to have as a trailing character or string.  Then you import into the incoming data into TheData.  

This should let you select the data from the table in the manner that you need it just by concatenating the columns into one output column.

However, that being said, if you create the table as I indicated (and I would add an Identity column if it were me ;-), why does all of that data have to be all in one column?  Why not use it as it sits in the various columns?  You don't say what you are doing with it after it is imported but one of the basic rules of database/table design is to not have more thanone piece of data in a given column.  This looks like you are combining multiple pieces of data into that one column and, frankly, that is probably going to come back and bite you in your posterior.  At some point you are liable to need to select rows based upon a date or date and time and, when you do, you are going to have an ugly bit of SQL to accomplish what you need.  If you leave the additional pieces of data in their own columns, you will have greater success (to say nothing of ease) in creating the SQL to analyze or search the data.

Author

Commented:
Thanks huslayer:. Your solution worked. I have another question after this. I am using Multiple Flat files connection.

In few files, records are in this format
     A)  10.15.115.207 - - [14/Jun/2011:00:00:43 -0600] .....

In few files, records are in this format
      B) 17.0.0.1!10.10.21.771 - - [14/Oct/2011:11:18:18 -0600]

Depending upon the format of the record, we need to append the text in front of each record in the file
In case of Format A) append date + 10.33.28.137 usage: 17.0.0.1   in front of the record

In case of Format B) append date + 10.33.28.137 usage: in front of the record.

How I can be done this in your solution to check which file have what type of format. Depending upon the format, append text..

Also, the files have million of records. I want solution that is efficient..

Thanks



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial