SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic ETL with SSIS


Dynamic ETL with SSIS

Author
Message
shirishjani
shirishjani
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 50
can you tell me how to create bulk like that
shirishjani
shirishjani
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 50
i am new with this

can you explain me if

i have dynamic file like 123.txt ,456.csv and like that and want to load them in to sql server

How can i achive that.??
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142340 Visits: 19424
If you have a question about something not in the article, please start your own thread in the SSIS forum.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ranjith_s 65449
ranjith_s 65449
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 231
While testing I see a couple of items missing:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'RN_UPD'. (RN_UPD Table is Missing)

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Load_RN'. (Load_RN Table is Missing)

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.GetNextWednesdaysDate'. (GetNextWednesdaysDate SP is Missing)


Can you please add it? thanks!
s.doriss
s.doriss
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 77
The Load_RN table is referenced in a previous post comment.

The function is purposely missing as mentioned in the article because you may want to calculate your own date.

The RN_Upd table doesn't exist because this really needs to be formatted to the data dictionary of your file.

Hope that helps.
ranjith_s 65449
ranjith_s 65449
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 231
I see Load_RNFile in previous comments not Load_RN.
s.doriss
s.doriss
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 77
yes, these are the same. The Load_* tables are supposed to have the Id, EverythingElse fields but the naming can be arbitrary because you would want one for each file. Also you would want one table for the parsed output per file. Sorry for the misnomer in the example, hope that helps you.
Terry300577
Terry300577
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2123 Visits: 516
Excellent article. This is similar to something i have previously developed whilst working for a company which processes 10 - 1000's of data files daily.

having the information in the database made it so much easier to process and create new imports.

the one thing we didn't have was detailed info from our suppliers, we had to work this out and make loads of assumptions too.

we also had the added problem of managing excel files along with text format files.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38050 Visits: 14411
Thanks for sharing your success story. I like that in the end you got a working solution that serves your users and your company well. That's what we do.

While I appreciate the result, I would have gone about it differently. From my perspective many of the efforts duplicated what SSIS does for us for free, namely the file parsing. Also worth noting, once you move file parsing into the database you gave up the option for doing data cleansing, validation, multi-system data enrichment, lots of things, in the SSIS space. I realize that sometimes we go with what we know, but SSIS was built to do this kind of work, and dynamically too. I think a lot of folks consider using tools other than T-SQL when the task seems to stretch the limits of what T-SQL should be doing, but when it gets to a point where they just need to get something done they revert to T-SQL because that is what is comfortable to them. Crutches like xp_cmdshell (as one poster mentioned, not your article) make it easier to do that as well and point to that type of thought process. I applaud your use of SSIS to at least avoid accessing the cmd prompt and file system from within T-SQL, something I never condone.

Here is a technical paper from Microsoft explaining how to do everything in SSIS:

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Build%20a%20Metadata-Driven%20ETL%20Platform%20by%20Extending%20Microsoft%20SQL%20Server%20Integration%20Services.docx

I know not everyone will sign up for a pure SSIS solution but something along these lines is how I would have solved this problem. Granted, i have .NET development skills which are required for putting together the solution described in the paper and not a lot of DBAs do, nor is the technology a good choice in all shops because of that same fact. Someone has to support the code too, so that has to be considered as well.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
ahperez
ahperez
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 635
Thanks Sarah, your article is very intersting and gave me some ideas. I can't wait to play around with this. Most of my business data comes from vendor flat files, mostly fixed width, with anywhere from 50-400 fields. Data dictionaries or layout guides are usually available, although the vendors don't usually notify me when making format changes. Fun!

You are right, it is much simpler to use the database for transformations and logic. Thanks for explaining how to store and use the data definition and import criteria there. SSIS is generally slow and frustrating for testing and troubleshooting my wide files. Your solution is also one I can afford on my shoestring IT budget!

I use the field name and width with the concatenate function in Excel to quickly make the fields section of a script for a raw import table, then paste that into my SSMS script. A quick way to make fields in SSIS is to use BIDS Helper (free, available on CodePlex). I copy the field name and width and paste it into the BIDS Helper "Create Fixed Width Columns" window. These two things have saved me days of work during testing of a new vendor format.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search