September 15, 2010 at 1:46 pm
jcrawf02 (9/15/2010)
Will definitely have to watch that when I get home.Side note, advice seeking -
53 text files that we want to load into 53 diff tables, all with different file structures.
Should I even be considering one SSIS package to do all that? Or is that ridiculous?
All the files come at once from our regulatory body, and we have to reconcile with our current data and respond under unreasonable deadlines (you know, same old same old), so was hoping to automate at least the load and variance portions.
Sure, definitely consider one SSIS package to do it. SSIS is great for sorting the various files out and processing them into your database in the various ways needed. The part I'd consider doing differently is if you want it as one package or multiple packages.
If you're going to want to run parts of it without running other parts, I'd say make it multiple packages. If you're always going to run the whole thing, make it a single package. Then again, you can create sub-packages within the main package and use logic in the package for what will be run when, if it is programatically discernable.
I've done file imports with SSIS and with CLRs and I much prefer SSIS.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 15, 2010 at 1:47 pm
bitbucket-25253 (9/15/2010)
By the way for some reason going down is always slower and harder than going up.
Couldn't have anything to do with the fact that you already climbed the damn thing, and you're tired?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 15, 2010 at 1:48 pm
jcrawf02 (9/15/2010)
Ok, so here's what I'm thinking then:each filetype has a reportID which will be PART of the name, so
53 ForEachLoop Containers, with a filetype of *<reportID>*.txt - theoretically should look for any occurrences of that type of file in the folder (forgot to mention that we don't get ALL the files all the time)
hard-code the columns from each filetype in the Data Flow to move the data in
...etc
seemed kind of gigrungious, not sure if I'm missing something or if that's actually what I should do.
That sounds like a good approach.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 15, 2010 at 1:51 pm
jcrawf02 (9/15/2010)
Ok, so here's what I'm thinking then:each filetype has a reportID which will be PART of the name, so
53 ForEachLoop Containers, with a filetype of *<reportID>*.txt - theoretically should look for any occurrences of that type of file in the folder (forgot to mention that we don't get ALL the files all the time)
hard-code the columns from each filetype in the Data Flow to move the data in
...etc
seemed kind of gigrungious, not sure if I'm missing something or if that's actually what I should do.
I'd say 1 ForEachLoop container and check the file name in that container, sending it to the appropriate procedure based on the name.
If you have 53 file types and want to do specific transformations, you'll need 53 procedures to do the transformation. If you're doing a straight load, you might be able to use XML templates to interpret the data, one template for each file type.
How are the files formated, XML? Comma Delimited? Other Delimited? Freeform?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 15, 2010 at 2:01 pm
Stefan Krzywicki (9/15/2010)
jcrawf02 (9/15/2010)
Ok, so here's what I'm thinking then:each filetype has a reportID which will be PART of the name, so
53 ForEachLoop Containers, with a filetype of *<reportID>*.txt - theoretically should look for any occurrences of that type of file in the folder (forgot to mention that we don't get ALL the files all the time)
hard-code the columns from each filetype in the Data Flow to move the data in
...etc
seemed kind of gigrungious, not sure if I'm missing something or if that's actually what I should do.
I'd say 1 ForEachLoop container and check the file name in that container, sending it to the appropriate procedure based on the name.
If you have 53 file types and want to do specific transformations, you'll need 53 procedures to do the transformation. If you're doing a straight load, you might be able to use XML templates to interpret the data, one template for each file type.
How are the files formated, XML? Comma Delimited? Other Delimited? Freeform?
text, fixed width. wondered about the XML templates, but hadn't got any further than wondering. Don't have a handy reference for me, do you?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 15, 2010 at 2:01 pm
bitbucket-25253 (9/15/2010)
...By the way for some reason going down is always slower and harder than going up.
By the way, if going down is much faster than going up, you're falling.
:w00t:
September 15, 2010 at 2:07 pm
Steve Jones - Editor (9/15/2010)
bitbucket-25253 (9/15/2010)
...By the way for some reason going down is always slower and harder than going up.
By the way, if going down is much faster than going up, you're falling.
:w00t:
lol fits right into Grant's presentation...."slow compared to what?"
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 15, 2010 at 2:14 pm
jcrawf02 (9/15/2010)
Stefan Krzywicki (9/15/2010)
jcrawf02 (9/15/2010)
Ok, so here's what I'm thinking then:each filetype has a reportID which will be PART of the name, so
53 ForEachLoop Containers, with a filetype of *<reportID>*.txt - theoretically should look for any occurrences of that type of file in the folder (forgot to mention that we don't get ALL the files all the time)
hard-code the columns from each filetype in the Data Flow to move the data in
...etc
seemed kind of gigrungious, not sure if I'm missing something or if that's actually what I should do.
I'd say 1 ForEachLoop container and check the file name in that container, sending it to the appropriate procedure based on the name.
If you have 53 file types and want to do specific transformations, you'll need 53 procedures to do the transformation. If you're doing a straight load, you might be able to use XML templates to interpret the data, one template for each file type.
How are the files formated, XML? Comma Delimited? Other Delimited? Freeform?
text, fixed width. wondered about the XML templates, but hadn't got any further than wondering. Don't have a handy reference for me, do you?
Handy? No. In my notes somewhere at home? Most likely. The XML templates were how I imported comma-delimited files through CLR processes. I think in SSIS you'd want to set up each type as a FlatFile Source or perhaps an XML File Source. I haven't done it this way yet, but will be soon.
I think I can get you examples or references by the end of the week if that helps.
Though I'm thinking you might want to make this its own forum post so others can help and it'll be more easily findable.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 15, 2010 at 2:19 pm
yeah, think I'll do that. Thanks guys.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 15, 2010 at 2:19 pm
jcrawf02 (9/15/2010)
bitbucket-25253 (9/15/2010)
By the way for some reason going down is always slower and harder than going up.
Couldn't have anything to do with the fact that you already climbed the damn thing, and you're tired?
Or that you are constantly looking down?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2010 at 2:22 pm
CirquedeSQLeil (9/15/2010)
jcrawf02 (9/15/2010)
bitbucket-25253 (9/15/2010)
By the way for some reason going down is always slower and harder than going up.
Couldn't have anything to do with the fact that you already climbed the damn thing, and you're tired?
Or that you are constantly looking down?
It has more to do with the fact that you're going backwards.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 15, 2010 at 2:23 pm
jcrawf02 (9/15/2010)
yeah, think I'll do that. Thanks guys.
If you don't mind, post a link here back to your new post and I'll follow it with the information I manage to dig up for you.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 15, 2010 at 2:24 pm
jcrawf02 (9/15/2010)
yeah, think I'll do that. Thanks guys.
Post a quick message in here when you do so that we'll know to go looking for it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 15, 2010 at 2:24 pm
Alvin Ramard (9/15/2010)
CirquedeSQLeil (9/15/2010)
jcrawf02 (9/15/2010)
bitbucket-25253 (9/15/2010)
By the way for some reason going down is always slower and harder than going up.
Couldn't have anything to do with the fact that you already climbed the damn thing, and you're tired?
Or that you are constantly looking down?
It has more to do with the fact that you're going backwards.
So you think it'd go faster if you climb down head-first like a squirrel? : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 15, 2010 at 2:25 pm
Alvin Ramard (9/15/2010)
jcrawf02 (9/15/2010)
yeah, think I'll do that. Thanks guys.Post a quick message in here when you do so that we'll know to go looking for it.
Heh, put it much better than I managed. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 18,661 through 18,675 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply