0

I am fairly new to SSIS and I have been looking everywhere for the answer to this question and can't find it, which makes me think its really simple and obvious, because I'm pretty sure this is a standard problem with SSIS.

I am building a SSIS package to automate the uploading of data.

We have a multi-instance environment across four servers and are using SQL Server 2005. I therefore have a user variable for the server name and instance name. The database and table will always remain the same. The data is held in an excel file, but I will import the data using CSV.

Is there a way for me to update the user variables from the CSV file? Is TSQL - 'Open rowset' the way forward?

I had previously been updating the variables from the table I had imported the data into, but then I realised in a live situation I wont know where to import the data to, as the values will still be in the CSV file.

Please help! This is driving me crazy and I have a sinking feel that the answer is really obvious which is making it worse!!

Thank you!

Julie

JYatesDBA
  • 25
  • 1
  • 10
  • So what is the purpose of the user variables for server and instance name? Am I to understand there is information in the source file that indicates what the destination should be? Could you clarify what the problem is that you are trying to solve? – billinkc Oct 05 '11 at 02:14
  • Yes that's right. Because we have so many instances I need to be able to update the instance and server locations every time I do a data upload. My package works at the moment but I have to manually update the config files every time I run the package, The source file holds the new values for the user variables, it was just updating the variables from the source file without using tables that I was struggling with. I have no knowledge of VB coding at all so will have to do a crash course but the solution below looks like it solves my issues, unless anyone else has any other thoughts! – JYatesDBA Oct 05 '11 at 07:46

1 Answers1

0

There is a good example here: http://vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2007/01/10/247.aspx of how to load a user variable from a flat file.

PaulStock
  • 11,053
  • 9
  • 49
  • 52
  • This looks great, thank you! I have virtually no knowledge of VB Coding so had been avoiding the Script Component task, but this looks like it resolves my problems so I'll have to stop being a wuss and do a crash course. Thank you!!! – JYatesDBA Oct 05 '11 at 07:53
  • You don't have to use VB in the script component. You can use C# instead, if you are more comfortable with that. If this answer is correct, please mark it as such by clicking on the checkmark to the left of the answer. Thanks – PaulStock Oct 05 '11 at 13:54
  • That worked! I now have lots of lovely green boxes and my data going into the correct location so THANK YOU! :D – JYatesDBA Oct 10 '11 at 15:20