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


ETL Magic with SSIS – Part I


ETL Magic with SSIS – Part I

Author
Message
GreyBeard
GreyBeard
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 331
Comments posted to this topic are about the item ETL Magic with SSIS – Part I
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54752 Visits: 21216
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sigerson
Sigerson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1111 Visits: 1237
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
David Data
David Data
SSC Eights!
SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)SSC Eights! (895 reputation)

Group: General Forum Members
Points: 895 Visits: 828
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.
power_unit
power_unit
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
ahernan
ahernan
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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!
kyle.lewis
kyle.lewis
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 64
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.
marko.stelzner
marko.stelzner
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 89
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 ;-)
Kevin Dahl
Kevin Dahl
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 313
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...
Misha_SQL
Misha_SQL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1700 Visits: 1011
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.



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