Is it possible to CREATE a database using SSIS and name it using a variable / parametermapping. if not, how can I use SSIS to take a username from a database table and create a database with that name? thanks is advance....b

Could you tell us why exactly you want to do this from SSIS? WesleyB Visit my SQL Server weblog @ http://dis4ea.blogspot.com

You can build your create database SQL statement in a variable expression and then use that variable as the SQL source for the Execute SQL task.

Wesley, I am trying to take the username from the UserData db table andCREATE a db to hold user files. And then using SSIS, upload data to the newly created user db and automate cube creation. Further, I want to trigger this process using a trigger on the UserData db table that causesSQL Server Agent to execute the job. The problem I am encountering is that whenever I use a parameter (in sql) or a variable in SSIS I get an error. Can the SSIS Execute SQL Task control flow item CREATE or ALTERa database? Can you use a parameter/variable as the name property in a CREATE or ALTER DATABASE command? thanks....b

Phil, Seem to have a problem using a paramter/variable as the nameproperty in aCREATE or ALTER DATABASE command. neither CREATE DATABASE @UserName nor ALTER DATABASE UserDB_new MODIFY NAME = @UserName work. Both give me a syntax error near @UserName. Thanks for any help.....b

Phil was suggesting setting the Execute Task SQLSourceType property to variable, and creating a variable that evaluates as an expression. In the expression, build the appropriate SQL string for creating the database.

I do this routinely with a create table... I'd imagine its the same thing. Instead of Create Database @DatabaseName use declare @myName nvarchar(50) declare @myCreate nvarchar(100) SET @myName = 'Dbname' Set @myCreate = 'Create Database ' + @mydate execute sp_executesql @mycreate Hope this helps

I use these techniques to create a database and have been able to run the package in Visual Studio and SSIS Deployment but not using a jobto execute the package. The job can execute the stored proc to create the database but not via the packageSSIS Developer

You can also use a Script Task: public void Main() { String DBName = Dts.Variables["TmpViewSourceDBName"].Value.ToString(); String Instance = Dts.Variables["TmpViewDBInstance"].Value.ToString(); String DBCreate; SqlConnection myConn = new SqlConnection("Server=" + Instance + ";Integrated security=SSPI;database=master"); //Need to Add If Not Exists Logic DBCreate = "CREATE DATABASE "+DBName+" ON PRIMARY " + "(NAME = "+DBName+"_Data, " + "FILENAME = 'C:\\"+DBName+".mdf', " + "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = " + DBName + "_Log, " + "FILENAME = 'C:\\" + DBName + ".ldf', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)"; SqlCommand myCommand = new SqlCommand(DBCreate, myConn); try { myConn.Open(); myCommand.ExecuteNonQuery(); } catch (System.Exception) { Dts.TaskResult = (int)ScriptResults.Failure; } finally { if (myConn.State == ConnectionState.Open) { myConn.Close(); } } Dts.TaskResult = (int)ScriptResults.Success; } } }