<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Ron Johnson
»
ETL Magic with SSIS – Part I
15 posts, Page 1 of 2
1
2
»»
ETL Magic with SSIS – Part I
Rate Topic
Display Mode
Topic Options
Author
Message
GreyBeard
GreyBeard
Posted Wednesday, January 04, 2012 9:20 PM
Valued Member
Group: General Forum Members
Last Login: Monday, March 11, 2013 8:52 AM
Points: 65,
Visits: 321
Comments posted to this topic are about the item
ETL Magic with SSIS – Part I
Post #1230483
Phil Parkin
Phil Parkin
Posted Thursday, January 05, 2012 2:34 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240,
Visits: 9,487
My comment relates to this sentence:
1) to fail the package if the file is not available, then execute the package again at a later time, or 2) we can wait for the file to be ready. EDIT- I have reconfigured the final sentence of this paragraph, as it was previously very hard to understand. Confirmation needed that my interpretation is correct
There is no need to fail the package if the file is not present. You have the option of using a Foreach container to process the file if it exists, otherwise complete the package successfully. You may also need to set delay validation to true on the file connection (can't remember for sure - haven't done this for a while).
It also seems that I am an SSIS purist
____________________________________________________________________________________________
Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1230559
Sigerson
Sigerson
Posted Thursday, January 05, 2012 6:11 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 171,
Visits: 541
I'm still learning SSIS, but this has a lot of good information and tips. Looking forward to Part II.
Sigerson
"
No pressure, no diamonds.
" - Thomas Carlyle
Post #1230683
David Data
David Data
Posted Thursday, January 05, 2012 6:37 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:28 AM
Points: 115,
Visits: 719
The File Watcher Task is a new one for me; could be useful in future. At present, my ETL tasks run once or twice a day and as Phil says above, tests if files are present, loading them if so.
Although I do use the Flat File Source component, I often have to replace it with a Script component written to deal with real files which often have bad rows or other oddities which confuse the standard Flat File Source. With Flat File Source, your imported file must always have the number of fields expected, and if comma-separated it can't have embedded quoted commas - e.g. a field like "19, The Strand" or """The Elms"", 4 High Street" will confuse it.
Post #1230701
power_unit
power_unit
Posted Thursday, January 05, 2012 7:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 05, 2012 7:35 AM
Points: 2,
Visits: 6
Thanks for this end to end example.
For what it's worth, I did a similar exercise using generated data I modified in Excel. There are a number of generators out there, useful perhaps for creating test data for real test cases.
http://www.generatedata.com/#generator is one.
Post #1230759
ahernan
ahernan
Posted Thursday, January 05, 2012 7:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, February 07, 2012 8:07 AM
Points: 3,
Visits: 16
I really enjoyed your article. I'm very new to the SQL Server scene and so this was very helpful. Much of what I've been reading insofar as SSIS and ETL talks about working from flat files. This includes your article. Is there any reason for this (i.e. best practices, performance, security...)? Reason I ask is because we're considering using SQL server to build mini data mart with transaction data that resides on 2 distinct Oracle databases. Are there reasons why we should load data using a flat file instead of database links if we know that all the servers/databases are sitting behind our firewalls? Database links seem like the easiest, most efficient way to doing this but, in the short time I've been researching this, I haven't read many articles about people using database links for this. Thanks Again!
Post #1230761
kyle.lewis
kyle.lewis
Posted Thursday, January 05, 2012 8:16 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 1:41 PM
Points: 13,
Visits: 61
You can definitely use SSIS to connect to different databases. We pull data from SQL, Oracle, DB2, and Informix. You can even utilize SSIS without using SQL as a source or destination.
The normal reason for using text files in scenarios is the ease of set up and configuration. Everyone is able to create a text file on their system. While creating a table in a different database may be restricted.
Post #1230794
marko.stelzner
marko.stelzner
Posted Thursday, January 05, 2012 1:04 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:42 AM
Points: 167,
Visits: 65
Good article and I'm looking forward to the sequel ...
Property "delay validation" is a very important note: before an SSIS package is run, is always a check. Without this feature, it is not possible, to create a table in the package and fill them with data - in one step. The search for validation errors can cost a lot of time... I know this
Post #1231094
Kevin Dahl
Kevin Dahl
Posted Thursday, January 05, 2012 3:13 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:51 PM
Points: 24,
Visits: 235
Hey Ron,
It's interesting that I came across your article when I did, as I'm currently cursing SSIS on a regular basis in my day to day worklife.
I've found in practice that the SSIS workflow tends to be a lot less fluid than what it seems in any of these small demonstrations. I'm currently involved in a huge data conversion project where we are making use of SSIS for the majority of the conversion efforts, and I'm about ready to jump out the window.
Even the simplest thing such as reading a flat file seems to fall apart in many real world scenarios. Take reading a large data file where the types cannot be easily determined by the first 10,000 rows, for instance - as that's the MAX that the flat file suggest types function will look at. So you're relegated to not making use of slick auto-magic features that are demonstrated, and rather stuck typing it all in by hand, assuming you know the max lengths and sizes you need for everything - if not you're stuck with trial and error, or using a third party program to do the analysis, and THEN typing it all in by hand.
How does anyone else get around these kind of setbacks that appear all to common with SSIS? Or am I the only one using SSIS who's not getting paid by the hour?!? Inquiring minds want to know...
Post #1231173
mishaluba
mishaluba
Posted Thursday, January 05, 2012 10:16 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525,
Visits: 617
Thank you for a great article! However, the quality screenshots is absolutely horrible. I wonder if this is something that can be fixed, so the words in the pictures are readable.
Post #1231253
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
