Not include the database name in the execution string when using the Import Export Wizard?

Question

I have a process where data is transferred from an Excel spreadsheet into SQL Server. To do this, I've created a dtsx using the SQL Server Import / Export Data wizard.

The trouble is, the script could be run against any database. But the wizard seems to default to using the format CREATE TABLE [Database].[dbo].[table] when creating the script. This means that the dtsx's "DestinationConnectionOLEDB" Connection Manager is worse than useless - you change it, thinking you've changed the end location that the script will run, only to find that the tasks still point to the original database.

What I've been doing up till now is manually going in and find-replacing [Database] for the new name. Sadly, it took me till today to realise that I probably could just find-replace "[Database].[dbo]." with an empty string (largely because I've had the dtsx's break a few of the times I've meddled with them).

So, is it possible to skip over the necessity of the final step (and reduce the possibility I'll break something along the way) by not including the [Database].[dbo]. in the file in the first place?

Answer 1

If you have standard or enterprise version you could:

1. Open the dtsx in BI studio as an SSIS package
2. In SSIS\Package Configurations enable configurations and use XML configuration file.
3. Select properties to use in configuration -- your connection string.
4. In the Project\Properties\Deployment Utility set AllowConfigurationChanges to True and CreateDeploymentUtility to TRUE
5. Build the project; a deployment folder is created with dtsx, xml and SSISDeploymentManifest
6. Copy the folder to your destination machine/folder
7. Double click the manifest file and it will prompt you for configuration parameters and deployment target.
8. Start your newly deployed dtsx.