August 20, 2007 at 8:48 am
I have created a package that does the following:
1. Creates a new file system folder in the Temp directory on the C: drive and given a unique name
2. A remote SQL Server db is queried and the results exported to a file that is saved to the folder created in step 1.
3. A third party dll function is called that does stuff!
The package has three connections, an ADO.NET connection, FlatFile and File connections. The query in step 2 is called using credentials of a user who is not part of the sysadmin group but has sufficient rights to read and write data for certain databases.
When I run the package in development manually it succeeds without problem. The problems start after I deploy the package. SSIS is new to me so I'd better explain how I have deployed the package:
1. Create a deployment utility by setting the appropriate value in Solution Explorer and then Build
2. Copied the manifest and dtsx files to the target server
3. Right click - Deploy on the manifest file (did this remotely on the target server)
4. Tried using File System and SQL Server deployments but always end up with the same error.
5. During the validation I get an error telling me that the account I specified in the ADO.NET connection failed to login ..... at System.Data.SqlClient.SqlInternalConnection....
Please note that the above is pretty much all I've done, I've not configured security or anything like that - is there anything else I need to do?
Thanks
August 28, 2007 at 6:15 am
I've worked out what the problem is but I don't know what to do to fix it.
The problem is being caused by a connection manager within the package; when executing the package from SSMS (after deployment) if I edit the connection manager I see that the password is missing. Adding the password within the connection string enables the package to execute.
So, what is the best way to deploy a package that retains the connection information that I specified within the package using BIDS?
Thanks
August 28, 2007 at 8:42 am
Try to pass the connection string at run time through activex script. You can do that by setting some variables to point to the connection string parameter and passing the variable value through activex script as the first step in your package.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply