May 12, 2009 at 3:24 pm
I have 4 databases on 2 other linked servers (SS2000) and I'll be importing data with the exact same structure into the same database in SS2005.
Is there a clean way to allow a user to run the package (even in VS2005) and give them an option like a checked list box of which servers they want to import from?
And what would be the best way to get the data from 4 databases into a common structure without just copy and pasting the import 4 times and changing the values? That would be a pain to upkeep if something changed.
Thanks for any advice you can offer! I hear amazing things about SSIS and I'm just trying to figure out how to leverage them 🙂
May 12, 2009 at 3:59 pm
I don't know about any GUI for the user, but as far as retargeting an SSIS package, that is usually done through Configurations. I do this all the time, though it is quite a bit more complicated than this (my packages have to be location, source and target unaware/specifiable at run-time as an enterprise standard, lots of extra work there).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 4:26 pm
You, sir, are all over the place!
I'm looking into package configurations right now.
Is it even possible to have 4 ds's (data sources) going into one data transformation? Or would I have to import each of the 4 ds's into a staging table in one data flow and then pick up with another data flow handling that staging table transforming to a live table?
May 12, 2009 at 5:59 pm
Hmm, Im not sure, actually...
What I was talking about is one package with a source CM (connection manager) pointing to one of your four sources and sending the data to you target ourput. You write that, get it to work, debug it, etc.
Then you change the connection source or the connection string to an expression that pulls from a certain variable. Then you set up a configuration variable (this is a DOS environment variable) that maps to that variable.
Then you write a wrapper (DOS command file or .Net Gui app) that DTExec's the package four times, once for each source. Each time it does this, it sets the corresponding DOS environment variable to the connection source name (or connection string) of one of the four sources, in sequence.
I have not done it before, but I think that you can do the same thing through a SQL Server Agent Job and it might be even easier.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 6:07 pm
Okay, that sounds easier than I thought. Just do the import for one, but make sure you're using the Package Configuration to store all the variables like server info.
I was imagining having a single xml file per set of configurations and then having to run one then after that, swap xml files and run again, etc. Not the easiest thing for an unsupervised import.
May 12, 2009 at 7:14 pm
Shawn Therrien (5/12/2009)
Okay, that sounds easier than I thought. Just do the import for one, but make sure you're using the Package Configuration to store all the variables like server info.I was imagining having a single xml file per set of configurations and then having to run one then after that, swap xml files and run again, etc. Not the easiest thing for an unsupervised import.
If you have to change more than one or a few variables, then yes, you have to go to configuration files or else configuration entries in SQL Server. However you can set them up as "indirect configurations" that means that the confg files path & file names are themselves located in environment variables.
But yes, this all starts to get increasingly complicated, them flexibility and location-independece that you want to build in. My recommendation, however, is to keep it as simple as possible early on.
If your four sources are all identical tables in SQL Server DB's, and the target output is unchanging, then you can get away with just two configuration variables: SourceDB and SourceTableName, both of which would feed into an OleDB Source CM.
SSIS is capable of great things, but the initial learning curve is quite steep and your first couple of packages are likely to be a minefield of details and gotchas, so my advice is always use KISS (Keep It Simple, S..) in the beginning.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 10:35 am
When I turn on Package Configurations (and use a variable) I start getting an error: DTS_E_PRODUCTLEVELTOLOW.
I don't have SQL Server or SSIS installed on my machine, which seems to be the issue. I just have the SQL Server Management Studio and the Visual Studio Business Intelligence Studio.
Is this correct that I need SSIS installed? Normally everything is run against a server so this has never been an issue unless network connectivity was lost.
May 13, 2009 at 11:17 am
Shawn Therrien (5/13/2009)
When I turn on Package Configurations (and use a variable) I start getting an error: DTS_E_PRODUCTLEVELTOLOW.
In what context are you getting this error? When you open/edit the package? When you try to test it from BIDS?, When you try to test it outside of BIDS?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 1:26 pm
Outside of the BIDS. Inside I'm fine. I was trying to change an xml value and run the DTS package when I got the error.
Yes, inside the studio things work, but I'm only assuming that it's using the xml file without being able to test it.
May 13, 2009 at 2:14 pm
Then this error eithe indicates that the run-time environment does not have SSIS installed or that the installed edition is too low (ie., Express when you need Standard, or Express or Standard when you need Enterprise). AFAIK, Configurations are not an Enterprise feature, so that should not have changed anything.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply