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


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.  ' 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.


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 YousefSr. BI  DeveloperCommented:
If I understand correctly, Use a "derived column" and whatever you need using an expression a

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 usage:!” in
the sql column RawData1

RawDataColumnValue   textChangeATMCredit

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

How can i do this with derived column
Jason YousefSr. 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 usage:!" + RawDataColumnValue + textChangeATMCredit

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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. - - [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 + usage:!' + RawDateColumn + '

How i can accomplish this. I just have one column only. No 2 columns
Saroj13Author Commented:
here is my expression:
Its giving me the error:

"'" + " " +  (DT_WSTR,4) @[System::CreationDate]  + " " +  " usage:!" + " " + 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
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
Jason YousefSr. BI  DeveloperCommented:
I thought you have 2 columns !   try that..

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

Open in new window

Jason YousefSr. 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 !

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

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

Jason YousefSr. 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 + usage:
5-use the newly created column instead of the old one in the flow.
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.
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) - - [14/Jun/2011:00:00:43 -0600] .....

In few files, records are in this format
      B)! - - [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 + usage:   in front of the record

In case of Format B) append date + 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..


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.