March 25, 2010 at 4:13 pm
NOTE: Due to a NDA I can't disclose any actual DDL for our DB but I hope that will not stop me from getting some help with this.
SSIS PURPOSE: To import 1 to N number of csv files into a single table on a daily basis.
SPECS:
The file type is always a csv file and the first row in every csv file will be a header row and that header row will always be the same in every file. The difference between all the csv files other then the file name is how many rows the file has and the data in each of those rows in each file.
I don’t need to do any data scrub within the SSIS I just need to take the contents of all the csv files in the specified directory and dump them into a single table. Because of the consistency with the csv files structure and m,y not needing to do any data scrub I thought this would be easy in SSIS but I was quickly halted when I found that there is both an Multiple Files connection and a Multiple Flat Files connection without any explanation as to the difference between them or any hints as to which to use and when.
If there were an easy way to take an Import Script done via the Import/Export data wizard in SSMS 2005 and convert it to process all files in a directory then my job would be %90 done but I don’t see a way to do that and so I’ve started from scratch with SSIS.
I’m open to any and all help/input on how to go about doing this in SSIS.
Thanks
Kindest Regards,
Just say No to Facebook!March 25, 2010 at 4:29 pm
Use Import/Export wizard in SSIS to complete the Data Flow Task part. You will be left with Control Flow task and all you need to do is use a For Each Loop and place the data flow task in the FEL and provide your Directory path and File Type which would be *.csv if you want to import all files in that dir or use a different pattern if necessary... Alo, you need to change few other things as in the link below. That will give you a good start
March 25, 2010 at 5:10 pm
divyanth (3/25/2010)
Thanks for the rpely but once again I am reminded why I hate SSIS so much. Thank God not everything in the technical world works like SSIS. If writing a SQL Query followed the SSIS process then in order to write a T-SQL Query that would return the text "Hello World" I would have to
1) Define a complete ASCII character set whether I need to use all the characters or not and NO you can't use some default or template set.
2) Provide the DDL to create a base DB with at least 1 user Table even though you aren't storing any data.
3) Set up 2 dozen pointers and aloocate space for both 32 bit & 64 bit data types (how is this even related to T-SQL??)
4) Finally, I'd have to put together a SQL proposal and prey that the query optimizer doesn't find any grammar mistakes or I go back to base0 and do not collect anything along the way.
Man I hate SSIS. Why can't the darn thing be just a little logical? I bet if we put 2 identical twins in different rooms I could teach mine the basics of quantum mechanincs before a SSIS guru can teach teh other to do the SSIS equivelant of the 'Hello Wordl' program sample. This assumes there is an actual SSIS guru verses people who have just sweated out enough years on SSIS that they know what not to do.
Seriosuly, now that I've ranted... I looked at the example you referenced and downloaded the sample file the author provided but its still not clear. What I rally need is an on line sample/example walk-thru that takes you step by step thru every click and setting you gott ado, assuming absolutely nothing, on how to add a ForEachLoop to an existing flat file import where the destination is a Dtabase table.
Know of anything like that?
Thanks
Kindest Regards,
Just say No to Facebook!March 26, 2010 at 9:17 am
I looked at the example you referenced and downloaded the sample file the author provided but its still not clear. What I rally need is an on line sample/example walk-thru that takes you step by step thru every click and setting you gott ado, assuming absolutely nothing, on how to add a ForEachLoop to an existing flat file import where the destination is a Dtabase table.
Know of anything like that?
I thought that link would give you a good start on SSIS... with the help of import/export wizard were you able to build the part where you can load the data from a file to a table? If so, just save the package and open the package with BIDS... You will see a DATA FLOW TASK in the CONTROL FLOW.. All you need to do is drag and drop the FOR EACH LOOP container to the Empty space and FOLLOW THE STEPS IN THE LINK and then drag the DATA FLOW TASK that you created into the FOR EACH LOOP ...
IT'S THAT EASY!!!
STEP 1:
Example 1: This link describes on how to Import data from File to OLE DB destination Table... You just need to omit the part where the author Creates a Table in link 2 because you already have one....
http://www.sqlyoga.com/2009/10/sql-server-ssis-basic-example-of-data.html]
STEP 2: create a FOR EACH LOOP and change the Directory path and File Type and then create a variable and change connection properties as it describes in the link below
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx]
If you still have trouble just stick in the screenshots along with the error
March 29, 2010 at 9:18 am
divyanth (3/26/2010)
I looked at the example you referenced and downloaded the sample file the author provided but its still not clear. What I rally need is an on line sample/example walk-thru that takes you step by step thru every click and setting you gott ado, assuming absolutely nothing, on how to add a ForEachLoop to an existing flat file import where the destination is a Dtabase table.
Know of anything like that?
I thought that link would give you a good start on SSIS... with the help of import/export wizard were you able to build the part where you can load the data from a file to a table? If so, just save the package and open the package with BIDS... You will see a DATA FLOW TASK in the CONTROL FLOW.. All you need to do is drag and drop the FOR EACH LOOP container to the Empty space and FOLLOW THE STEPS IN THE LINK and then drag the DATA FLOW TASK that you created into the FOR EACH LOOP ...
IT'S THAT EASY!!!
STEP 1:
Example 1: This link describes on how to Import data from File to OLE DB destination Table... You just need to omit the part where the author Creates a Table in link 2 because you already have one....
http://www.sqlyoga.com/2009/10/sql-server-ssis-basic-example-of-data.html]
STEP 2: create a FOR EACH LOOP and change the Directory path and File Type and then create a variable and change connection properties as it describes in the link below
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx]
If you still have trouble just stick in the screenshots along with the error
Just wanted to say thanks again and let you know that I finally got this working. I ended up going thru a walk-thru sample in the WROX book SQL Server 2005 Integration Services and that worked. What seems obvious now that I know the answer was not so obvious before and that is the relative location of the ForEach loop. By that I mean when you add the ForEach Loop to the package do you drag it on to the work area and then drop the connection inside of the loop or do you drop the ForEach Loop on top of the Connection ?
I have to say that even after I now know how to do this I still take the stance that this is far from intuitive to one who doesn't know how to use SSIS. I realize that they built SSIS like they did so as to make it powerful and flexible but personally I believe MS did not put enough emphasis on ease of use. They have done this with other power techs like PowerShell and to a lesser extent the whole .Net programming revolution but that’s a completely different topic.
Thanks again for helping.
Kindest Regards,
Just say No to Facebook!March 29, 2010 at 9:40 am
you add the ForEach Loop to the package do you drag it on to the work area and then drop the connection inside of the loop or do you drop the ForEach Loop on top of the Connection ?
Good to hear that you got it working.. Well both ways work, but i would prefer adding the ForEach and then drop the connection inside.. Assuming that you are referring to a Data FLow Task or control Flow task item by saying "Connection". Am i right?
March 29, 2010 at 11:27 am
divyanth (3/29/2010)
you add the ForEach Loop to the package do you drag it on to the work area and then drop the connection inside of the loop or do you drop the ForEach Loop on top of the Connection ?
Good to hear that you got it working.. Well both ways work, but i would prefer adding the ForEach and then drop the connection inside.. Assuming that you are referring to a Data FLow Task or control Flow task item by saying "Connection". Am i right?
Yes you are correct in yoru assumptions. I still have a ways to go with the terminology. I hate that SSIS is so counter intuitive because it was one of the more 'most anticipated' things on my SQL Server 2005 fun list and when it came out and I found out what SSIS was like I found myself putting it off again and again, choosing alternate solutions whcih took less time to do even when doing tehm with SSIS woudl be faster once you mastered how to use SSIS. Its just a big learning curve with SSIS. I wish I had weeks of paid time off to learn it right but I don't.
Thanks
Kindest Regards,
Just say No to Facebook!January 28, 2012 at 8:55 am
I think the issue is that you obviously can not spell worth a twit. This makes it highly unlikely that
you know how to:
1) Follow the rules
2) Imitate a pattern
3) Retain knowledge or information
4) Pay attention in class
5) Remain alert, attentive and observant
6) Become adept in pattern matching
7) Know how to use a spell-checker
8) Get good grades in spelling, grammar or composition
9) Be dedicated to mastering a task
10) Take pride in your work and your mastery of a subject
ALL of these are required in order to learn SSIS and retain what you have learned.
September 19, 2013 at 2:14 am
Hi,
You need just a flat file source and ole db destination in data flow.
Also one multiple flat file connection manager and one connection manager to connect your db.
Now,in multiple flat file connection manager ;
In general tab choose your all csv files.You must define your "header row delimiter" as "vertical bar {|}" and format "demilited",if your files are delimited.
If your files have column name also click "columns names in the first row" in general tab.
Thats all.Run it.
September 19, 2013 at 12:35 pm
John Bevilaqua-427875 (1/28/2012)
I think the issue is that you obviously can not spell worth a twit. This makes it highly unlikely thatyou know how to:
1) Follow the rules
2) Imitate a pattern
3) Retain knowledge or information
4) Pay attention in class
5) Remain alert, attentive and observant
6) Become adept in pattern matching
7) Know how to use a spell-checker
8) Get good grades in spelling, grammar or composition
9) Be dedicated to mastering a task
10) Take pride in your work and your mastery of a subject
ALL of these are required in order to learn SSIS and retain what you have learned.
@johnny Boy
1)How about reading the rules before trying to tell someone else they need to follow them
2) I placed the contents of my original post into Word and ran spell check and there was one misspelled word and a couple of grammatically incorrect portions. Some of my follow-up posts were less grammatically proper because I was in a hurry to follow-up back with those who replied because I valued their opinion (unlike yours) and therefore wanted to reply as quickly as possible. Since you want to be grammatically anal I would like to point out that your perfect post has grammatical errors.
3) The rest of your post is nothing short of a diatribe from a mean spirited asz so go ….
Kindest Regards,
Just say No to Facebook!September 19, 2013 at 12:36 pm
kocamisu (9/19/2013)
Hi,You need just a flat file source and ole db destination in data flow.
Also one multiple flat file connection manager and one connection manager to connect your db.
Now,in multiple flat file connection manager ;
In general tab choose your all csv files.You must define your "header row delimiter" as "vertical bar {|}" and format "demilited",if your files are delimited.
If your files have column name also click "columns names in the first row" in general tab.
Thats all.Run it.
Thanks Kocamisu for the suggestion and taking the time to reply constructively unlike some others.
Kindest Regards,
Just say No to Facebook!September 19, 2013 at 1:03 pm
YSLGuru (9/19/2013)
kocamisu (9/19/2013)
Hi,You need just a flat file source and ole db destination in data flow.
Also one multiple flat file connection manager and one connection manager to connect your db.
Now,in multiple flat file connection manager ;
In general tab choose your all csv files.You must define your "header row delimiter" as "vertical bar {|}" and format "demilited",if your files are delimited.
If your files have column name also click "columns names in the first row" in general tab.
Thats all.Run it.
Thanks Kocamisu for the suggestion and taking the time to reply constructively unlike some others.
Its my pleasure YSLGuru
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply