[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS: Loading in a flat data file, parsing, sending data to SQL tables

Posted on 2010-08-25
12
Medium Priority
?
734 Views
Last Modified: 2013-11-10
I'm loading in a flat file with varying number of columns, but now I want to take the data, parse it out and upload it to SQL tables. Not sure how to build this. Anyone have any links with examples I could follow?

Thanks!!
0
Comment
Question by:wppiexperts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33526075
To apply changes to existing data, you need to use derived column transformations along with the data flow task. More info here: http://msdn.microsoft.com/en-us/library/ms137630.aspx and here : http://www.ssistalk.com/2007/01/23/derived-column/
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33526115
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33526146
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 33526254
I generally import the data into a staging table that has only VarChar columns.  That lets me deal with squirrelly data as well as creating INSERTs to move the data where I please after it is scrubbed.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33527868
when you want to import dynamic data into sql server, BCP is one of the best options. look at samples of BCP here:
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

0
 

Author Comment

by:wppiexperts
ID: 33531231
vdr1620: the link you posted about pulling in csv files with varying columns was exactly what I was looking for. At this point now that the data is loading, I'm trying to figure out how to push subsets of that data into a sql table.

For example, a row may contain a customer number, an item ID and then data pertaining to hourly use of that item, so I'd be splitting that row into 3 tables. Its at this point I'm not sure how to process that data, do I use tools within SSIS to do this or program it all in a script task.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33531311
wppiexperts,
Are you loading the data into a staging table or are you trying to do this on the fly, so to speak?
If you are using a staging table, then all you have to do is figure out waht columns go in which tables and create queries (or stored procs) to accomplish that.  You can then include those, via Execute SQL Tasks, in your SSIS package.
If you are doing it on the fly, then you are probably using a FOR EACH loop and handling the data RBAR (Row By Agonizing Row), which is absolutely THE worst way to do it, and you would still be using Execute SQL Tasks to accomplich the actual work.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33531390
So, According to what you have said.. You have a Flat File Source and Script task connected... You will need a MultiCast Transformation and then 3 OLE DB destinations one for each tables..Connect the green Arrow from Multicast to each of the destinations and map the required column accordingly
0
 

Author Comment

by:wppiexperts
ID: 33532981
came across this article which expands a bit on the link provided by vdr1620:
http://sqlblog.com/blogs/andy_leonard/archive/2010/05/18/ssis-design-pattern-loading-variable-length-rows.aspx

so, now on to multicast!
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33533211
Thats a nice Reference and might be suitable to what you are trying to do by avoiding multi task transformation as i suggested before..you can just create Multiple Outputs using Script task and map them to OLE DB Destinations directly
0
 

Author Comment

by:wppiexperts
ID: 33577850
OK - so I'm at the point where I have transformed the flat file data and I can load it into the sql tables. My next task is to take anwhere from 24-50 columns from the flat file and pivot them into rows of data. What tool in SSIS would be best suited for that....again...anyone have examples they can point me to?
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 33577982
You can use Pivot transformation ..Example is in the link below

http://www.bimonkey.com/2009/06/the-pivot-transformation/

You also have pivot in SQL ..SSIS Vs SQL pivot is compared in below link

http://bennyaustin.wordpress.com/2010/06/26/ssis-pivot-transform-vs-t-sql-pivot/
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 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