Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1877
Members Online:
3
Guests Online:
98

Total Post History
Posts:
81447
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
AlicjaHYBCIA

Other

FAQ
All times are UTC [ DST ]

Google Ads

SSIS - Dynamic Excel Source Files

Microsoft SQL Server
Please wait...

SSIS - Dynamic Excel Source Files

Postby Chopstik on Thu Jan 24, 2013 1:13 pm

Looking to do some proof of concept work within SSIS and gotten several things working but something I wanted to ask instead of searching all over for it right now. I know it should be possible to create a dynamic Excel source file but I am looking for a bit more. In short, I have a variety of Excel files that can be sent and I want to transform them into text files. The Excel files are not the same structure/format, they can have different numbers of columns along with different column headers. What I'd like to do is create a very flexible structure that will accept each file (the file name/location passed into the package each time it's called), read it and then export into a new flat file (tab-delimited text or even CSV).

I know that I can set up a package for each one but would prefer to have something more flexible. Just don't know if that's possible with SSIS. Thoughts are appreciated.

(And I'm sure it can be done if I write up something in C# or similar, but would prefer to use an actual ETL tool to handle it)
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: SSIS - Dynamic Excel Source Files

Postby Remou on Thu Jan 24, 2013 2:50 pm

You know this is a doddle with VBA or even VBScript and automation, I suppose?
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: SSIS - Dynamic Excel Source Files

Postby Chopstik on Thu Jan 24, 2013 3:34 pm

Remou wrote:You know this is a doddle with VBA or even VBScript and automation, I suppose?

Not really, but am open to suggestions. I can't say that was even one of my first thoughts as I haven't worked with them in a while but it's worth a shot. Can you be more specific or point me to some suggestions? Thanks.
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: SSIS - Dynamic Excel Source Files

Postby Remou on Thu Jan 24, 2013 4:09 pm

Some very, very rough notes from an Excel module to give you the keyword:

  1. Sub ToCSV()
  2. Dim wb As Workbook
  3.  
  4. sfile = RunFD("Z:\docs\", "Excel", "*.xl??", 3)
  5. ssave = Mid(sfile, 1, InStrRev(sfile, ".")) & "csv"
  6.  
  7. Set wb = Workbooks.Open(sfile)
  8. wb.SaveAs ssave, xlCSV
  9. wb.Close False
  10. End Sub
  11.  
  12. Function RunFD(strStartIn As String, sDescription As String, _
  13.     sExtension As String, intType) As String
  14. Dim dlg As Office.FileDialog ''object
  15. Dim astrFilter As Variant
  16.  
  17. ''msoFileDialogFilePicker=3
  18. ''msoFileDialogSaveAs=2
  19.  
  20. 'On Error GoTo TrapError
  21.  
  22.     Set dlg = Application.FileDialog(intType)
  23.    
  24.     With dlg
  25.         .Filters.Clear
  26.         .Filters.Add sDescription, sExtension
  27.         .InitialFileName = strStartIn
  28.        
  29.         If .Show Then
  30.             RunFD = .SelectedItems(1)
  31.         End If
  32.    
  33.     End With
  34.    
  35. Exit_Sub:
  36.     Exit Function
  37.    
  38. TrapError:
  39.     MsgBox Err.Number & " " & Err.Description
  40. End Function
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am

Re: SSIS - Dynamic Excel Source Files

Postby Chopstik on Thu Jan 24, 2013 5:08 pm

I'll play with this a little and see what I find. Thanks!
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: SSIS - Dynamic Excel Source Files

Postby onpnt on Mon Jan 28, 2013 6:30 pm

In SSIS you have a few options. If performance is an issue, you may want it to actually be multiple DTSX files so you can gain some perf on running multiple files at once. If that isn't a concern, you'll want to follow this flow
1.Call the DTSX with a parm of the excel file name
2.Set the excel source as the parm
3.Based on the parm, have a conditional precedence that sends you on the path to the 4.data flow that fits the mappings of that specific sheet
export to flat file

or

1.Call the DTSX with a parm of the excel file name
2.Set the excel source as the parm
3.Bulk the sheet into SQL staging
4.Work off the stage table with T-SQL to map everything
5.Bulk it out

It isn't all that difficult. I have some blogs on dynamic connections and doing similar things on LTD that should help
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: SSIS - Dynamic Excel Source Files

Postby Chopstik on Mon Jan 28, 2013 6:38 pm

Thanks, onpnt. I'll have a go at #1 since performance is not an issue from that standpoint (these will all be run one at a time). I'll find the blog posts you've mentioned and let you know if I have have any questions as I go through them (once I actually get the time to actually do this instead of the other fires I'm currently putting out). Thanks! :thumright:
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated