Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ETL Magic with SSIS – Part I Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2012 9:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 3:07 PM
Points: 65, Visits: 331
Comments posted to this topic are about the item ETL Magic with SSIS – Part I
Post #1230483
Posted Thursday, January 5, 2012 2:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 5,245, Visits: 12,161
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1230559
Posted Thursday, January 5, 2012 6:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:35 PM
Points: 284, Visits: 829
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
Posted Thursday, January 5, 2012 6:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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
Posted Thursday, January 5, 2012 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 5, 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
Posted Thursday, January 5, 2012 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 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
Posted Thursday, January 5, 2012 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:20 AM
Points: 13, Visits: 63
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
Posted Thursday, January 5, 2012 1:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:09 PM
Points: 182, Visits: 85
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
Posted Thursday, January 5, 2012 3:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 9, 2014 6:06 PM
Points: 26, Visits: 288
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
Posted Thursday, January 5, 2012 10:16 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 541, Visits: 788
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 »

Add to briefcase 12»»

Permissions Expand / Collapse