Solved

SSIS Package for Import Flat File

Posted on 2010-11-13
33
1,917 Views
Last Modified: 2013-11-10
I have a text file ( .txt ) which has following values ... ( may be 20,000 rows).
This file doesnt have No Column Name or Comma seperator..

This is the actual row.

000650397051015783      12.21        5.00

When i  import through SSIS Package, all these 3 columns comes in a single column as column0.

I want the result as 3 Columns ( Column1, Column2,Column3)
0
Comment
Question by:chokka
  • 14
  • 13
  • 3
  • +3
33 Comments
 
LVL 13

Expert Comment

by:markusdamenous
ID: 34128263
When defining the SSIS package, ensure that you opt for the fixed width import option.  You can then set how wide each column is, instead of it making an assumption that it is just one column.

Some further reading here:
http://www.sqlservercentral.com/Forums/Topic565098-148-1.aspx
0
 

Author Comment

by:chokka
ID: 34128321


I have kept a copy of actual file attached along this comment.

I tried using Fixed Width option in SSIS Package, which is not working.

Please have a look on  this file and advise me.
Test.txt
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34128732
Select "Ragged Right" and see what happens
0
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 250 total points
ID: 34130061
HI,

I created a word doc with the screens from the SSIS package I made using your sample.

 I imported it in one column and later on, I created the 3 different column using SQL, you can include it as a SQL task into your package.

Any questions, just ask.

Good luck
SELECT * FROM dbo.Test


SELECT SUBSTRING(data, 1, 18) AS Column1,
       SUBSTRING(data, 19, 11) AS Column2,
       SUBSTRING(data, 30, 12) AS Column3
INTO dbo.TestClean
FROM dbo.Test

SELECT Column1,
		CAST(Column2 AS FLOAT) AS Column2,
		CAST(Column3 AS FLOAT) AS Column3
 FROM dbo.TestClean

Open in new window

ETLsample.doc
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34130072
Ah, and the package itself,

I used Adventureworks DB into my local machine, adapt the connection managers to your needs.

Cheers
ExpertExchengeSample.exe
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34130586
I'm wondering why this question is not in RELATED zone, there is a SSIS Zone in EE!
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34130729
you can use Flat file source as Fixed Width and set width of each column and then you will have 3 separate columns
0
 

Author Comment

by:chokka
ID: 34132500
reza_rad :- i tried fixed width .. it doesnt work

raulggonzalez :-

I thinking importing into a Table 1 :- And then keep a trigger to transfer to Table 2 ..! This seems to be double step which can affect the performance.

But brilliant suggestion.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34133744
>>i tried fixed width .. it doesnt work<<
why?
could you upload your sample text file here and I tell you about how to configure flat file connection manager for it.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34134858
Hi,

If you want to import it into 3 columns, when you're defining the 'Flat File Connection Manager' -> Columns, Drag and drop the red line till the end so you can see the lines of your file.

Click after the end of every column and a black line will appear, so you have your three columns.

For the firs column I'd use data type string [DT_STR]
For the others, depends on what precision you need, but DT_DECIMAL or DT_R8 (float) should be ok.

good luck
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34135474
Hi,

User {Tab} as delimiter.
0
 

Author Comment

by:chokka
ID: 34136519
In the Comment ID: 34128321

I have  kept the sample .txt file.  
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 250 total points
ID: 34137537
OK,
your file is RAGGED RIGHT
set format in the flat file connection manager as Ragged Right
 Ragged Rightand then in columns tab,click on the ruler at the top of values and this will create columns for each separator.
 select columns
0
 

Author Comment

by:chokka
ID: 34137851
Okay, Great ..!

reza_rad :- Brilliant Suggestion - Thanks for your help

Great.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34137898
Glad to help,
Regards,
0
 

Author Comment

by:chokka
ID: 34157095
In the Comment ID: 34137537

You have enclosed Image 2.jpg

That helped me a lot, by drawing a line manually and seperate between columns.

Will it be possible for us to mention inside SSIS Package.

We need to entire process through an User Interface Program.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Expert Comment

by:Reza Rad
ID: 34157734
>>We need to entire process through an User Interface Program<<
I didn't get this part clearly
could you explain more...?
0
 

Author Comment

by:chokka
ID: 34157832
In the image 2.jpg, we need to manually draw the line between the width.

On drawing line, seperates the column.

Is there anyway for us to do through program.

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34157945
>>Is there anyway for us to do through program<<
why you want to do this through program? do you try to create whole package with programming?
maybe there be a way, But i have no experience on this special case.
tell me what exactly you want to do and maybe there be better way to do this
0
 

Author Comment

by:chokka
ID: 34157994
Please see the image 2.jpg

How the lines came ...between the column values ?


Please see the image attached. There is difference between this image and 2.jpg.

Difference is that there is a Line Seperator drawn.
ResultedImage.JPG
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34158071
click on the ruler at the top, on each point you want to set a separator.
and per each line you will have a separator
you can see yellow highlights on my second image , those are the points which I set separator.
try it and let me know if you have issue yet.
0
 

Author Comment

by:chokka
ID: 34158110
I understand that, and i am able to import.

In the Comment ID: 34137851 , I have mentioned about it.

But point is, is it possible for us to mention in the settings.

Because, every time, we need to import .. we need to draw the line seperator manually.


0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34158159
ok, I think you want to import multiple files,
and you have issue on set the columns for each file in flat file connection manager.
in other words you are able to do this for one file, but you want an automated procedure for multiple files,
Am I right?
if yes, tell me about the structure of files? are there all have same structure of data? I mean like this one has 3 columns with this ragged right fields and same length?
0
 

Author Comment

by:chokka
ID: 34158211
First of all you are partially right.

1) We are importing .txt file to Table.

2) I am developing a Tool.  Tool will have the file path. where you can upload the .txt file and import it to database.

3) When ever you feel to Import or Refresh your database with new set of records. You have to do  this Import Process.

PROBLEM With your Approach is ..!

We need to manually draw the LINE SEPERATOR on setting RAGGED RIGHT Feature in SSIS.

Is there anyway for us to set the feature for Line Seperator inside the Package.


0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34158555
You can do it with package variables,
First you create your pacakge for single flat file. and set flat file souce path with variable
then deploy the package,
then run the package from your application with set the variable value as your new flat file address.

does it make sense to you?

0
 

Author Comment

by:chokka
ID: 34159281
So,Let me create SSIS Project with Package in it.

Once done, i will be back to you.
0
 

Author Comment

by:chokka
ID: 34191142

Under Data Flow

I am trying  to connect the Source File ( Flat File Source ) and Desitnation File ( OLE DB Source ).

I am not able to create the connection.

Because Flat File doesn't have Column Name and Destination File has Column Name.
TITLE: Microsoft Visual Studio

------------------------------



Cannot create connector.

The destination component does not have any available inputs for use in creating a path.



------------------------------

BUTTONS:



OK

------------------------------

Open in new window

SourceEditor.JPG
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34191252
you should connect data path ( green arrow ) from flat file source to oledb destination and then map columns.
0
 

Author Comment

by:chokka
ID: 34191281

On calling the GREEN ARROW for establishing the Connection,

I am receiving the error ..! Eventually the Green Arrow disconnects ..!

So, relationship is not able to create.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34191445
could you upload your whole package here?
0
 

Author Comment

by:chokka
ID: 34191545
Okay i have done that.

Issue :-

I kept OLE DB Source - Instead of Destination.

So, there was an error
0
 

Author Comment

by:chokka
ID: 34191577
How to keep the Source File , Connections dynamically which can read from Config file in SSIS Project ?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34191786
you should create a variable for file source,
and then set filename of flat file connection with this variable,
and then you can use DTEXEC to run package with variable value.

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update foreign key reference after insert 9 35
Recurring Excel Timelime for Veeam 2 35
SQL JOIN 6 37
Mssql SQL query 14 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now