Dynamic package configurations

I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @.originalFilePath and @.destinationFilePath.

I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.

What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
Take a look at DTEXEC and the /SET option. Using that, you can call your pacakge and use SET to override the values of @.originalFilePath and @.destinationFilePath. No configuration file required. It does mean passing the values each time, but given your situation it sounds like that will be the best solution.|||Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?

I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
|||

papalarge wrote:

Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?

I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)

Strictly speaking, .Net 2.0 is required.|||any ideas why I get the error "A reference to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll' could not be added. This is not a valid assembly or COM component."?

I have Visual Studio 2003, and .NET 1.1 and 2.0 framework installed on my local machine.
|||Does any of this information help?

http://blogs.msdn.com/michen/default.aspx|||it does help, yeah. the part that says:

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).

So I guess I need VS 2005 in order to use the object model for SSIS?
|||

papalarge wrote:

it does help, yeah. the part that says:

Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs.

which means you need a license on each machine running the server. were you aware of that?

Dynamic Login & User Creation

Hello. I'm trying to create a new login and username inside a trigger using variables.

This code works:

create login testUserName with password = 'testPassword'

The problem comes when I try to use variables for the username and password like this.

create login @.username with password = @.password

Does anybody know how to get around this problem?

BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '@.username'.

Msg 319, Level 15, State 1, Line 14

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||

This doesn′t work unless you wrap it in dynamic SQL:

DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = 'testUserName2';

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||

You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.

Dynamic List

Is there a way to create a dynamic list? I have two variables I want to combine with two constants to create a list of 4 entries that I could run through a loop.You can use a script task to populate an object variable with an array. Then you can use the Foreach from Variable Enumerator to loop through the array. The script to populate the array could look similar to this (using your variables in place of some of the constants).

Code Snippet

Public Sub Main()
Dim LoopItems(3) As String
LoopItems.SetValue("One", 0)
LoopItems.SetValue("Two", 1)
LoopItems.SetValue("Three", 2)
LoopItems.SetValue("Four", 3)
Dts.Variables("LoopItems").Value = LoopItems
Dts.TaskResult = Dts.Results.Success
End Sub

Alternatively, you could also use an Execute SQL task with a UNION statement to populate a object variable with a recordset of your values and then use the Foreach ADO Enumerator. You would create your SQL statement using an expression-based variable.

|||

Thanks. I think creating a recordset in script might be my only alternative. I needed two columns for the four entries and For Each from a variable seems to only work with a single column. My problem was that I had two locations each of which had two types of files that needed deleting. The locations had to come from variables while the file types were constants. The entire solution in .Net was 7 more lines of code than you listed, I was just hoping to stay out of code.

Public Sub Main()

Dim directoryList As New List(Of String)

Dim patternList As New List(Of String)

directoryList.Add(CStr(Dts.Variables("principalLocation").Value))

directoryList.Add(CStr(Dts.Variables("mirrorLocation").Value))

patternList.Add("*.bak")

patternList.Add("*.trn")

For Each directoryItem As String In directoryList

For Each patternItem As String In patternList

For Each file As String In Directory.GetFiles(directoryItem, patternItem)

System.IO.File.Delete(file)

Next

Next

Next

Dts.TaskResult = Dts.Results.Success

End Sub

The problem is mostly that I can't have an empty directory or my daily maintenance will remove the directory. So I have a file in the directory called keepthis.txt. Another pure SSIS solution I guess would be to Delete the contents of the directory and then copy a keepthis.txt from somewhere else into that directory, I'd just have hope that the daily maintenance would never run at the same time this ran, something I'd rather not hope for with 12 people that can manually run this, usually under pressure.

Thanks for the solution though, I found the way you set array variables interesting, I hadn't seen that before!