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

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

Saroj13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason Yousef, MSSr. BI  DeveloperCommented:
If I understand correctly, Use a "derived column" and whatever you need using an expression a
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saroj13Author 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
0
Jason Yousef, MSSr. BI  DeveloperCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saroj13Author 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
0
Saroj13Author 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
0
Saroj13Author Commented:
one or both operands need to be explicitly cast with a
cast ... The data types "DT_WSTR" and "DT_TEXT" are incompatible  error
0
Jason Yousef, MSSr. BI  DeveloperCommented:
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

0
Jason Yousef, MSSr. BI  DeveloperCommented:
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 !

0
Saroj13Author 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
0
Saroj13Author Commented:
How I can  use split and merge?
Please help me in this regard. I am new...
0
Jason Yousef, MSSr. BI  DeveloperCommented:
how big is the column you're loading anyway? why do you need to select data type as TEXT?
0
Saroj13Author Commented:
Column may have 12,000 characters for each record
0
Alpesh PatelAssistant ConsultantCommented:
You can use Derived columns and add column in between the Data transfer from Source to Destination.
Capture.PNG
0
Jason Yousef, MSSr. BI  DeveloperCommented:

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

0
Jason Yousef, MSSr. BI  DeveloperCommented:
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
0
8080_DiverCommented:
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.
0
Saroj13Author 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



0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.