>What’s up with QA at Microsoft these days? I was trying to setup a Back Up Database task in an SSIS package when I caught something from the corner of my eye. Check out the name of the highlighted property:
>Should we send the Wizard back to Hogwarts?
>The SQL Server Import and Export Wizard in SQL Server 2005 is a handy tool to quickly transfer data from one data source to another, though on most occasions I use it to transfer data between two SQL Server instances. During these data transfer sessions, I always could not connect to a particular SQL Server instance using SQL authentication, though I could connect to it throught SSMS without any worries. The only difference between this particular SQL Server and the others was that, this one had the password as P@ssW0r;D. Whereas the others had simple passwords; either blip or bleep. So where would the problem lie?
Well you see, the Import and Export wizard in its internals uses SSIS, and within that it uses an OLEDB connection. An as usual an OLEDB connection uses a connection string, and when the password is given in the connection string… all hell breaks lose (if I were to exaggerate a little). Why you ask? Because the connection string would now have an extra ‘;’ in its midst:
Data Source=SERVERX;User ID=sa;Password=P@ssW0r;D;Initial Catalog=DBX
To correct this problem, I simply included double quotes at the beginning and at the end of the password in the password field of the wizard.
This worked like a charm, since the connection string in the internal workings of the wizard would now look something like this:
Data Source=SERVERX;User ID=sa;Password=”P@ssW0r;D”Initial Catalog=DBX
Now, this got me thinking: “What if my password contained a semi-colon (;) and a double-quote (“) ?”
Answer: I didn’t want to find out.
Data Maul 