[SSIS] In the script using database connection string to query and other processing, into the pit

into the PIT.!!!!!

SSIS package settings for ADO. NET connection, in the incoming script, I want to use the database connection, the data delete operation.

So I used the following code

Using Windows authentication, when publishing to the formal environment, can not use Windows authentication, and to use the account password to log in, the result I changed the account password after landing,

Inside the call place, Connection.Open when the error, said login failed. I looked at the connection string, without the password section.

This is the security of the DTS package or project set by Microsoft, and the password part of the connection string is not the explicit text. So this way to get the connection string, is not to be taken.

The following solutions are now available

First set two variables, then get Connection directly.

    IDTSConnectionManager100 connmgr;    SqlConnection sqlconn;

 Public Override void Acquireconnections (object  Transaction)    {
        //Get and set Connection connection          This . connections.destconnstr;         = (SqlConnection) connmgr.acquireconnection (Transaction);    }
     Public Override void releaseconnections ()    {        connmgr.releaseconnection (sqlconn);    }

  Public intExecuteNonQuery (stringSqlstr, CommandType cType, sqlparameter[] parms) {        intresult =0; Try{SqlCommand cmd=NewSqlCommand (); Cmd. Connection=sqlconn;//Direct use of data connection Cmd.commandtype=CType; Cmd.commandtext=sqlstr; if(Parms! =NULL) {cmd.            Parameters.addrange (parms); } result=cmd.        ExecuteNonQuery (); }        Catch(Exception ex) {result=0; Throwex; }                returnresult; }

Finally, don't forget despose.

public override void PostExecute ()    {        base. PostExecute ();         /* * ADD Your code here         *        /if (sqlconn! = null)            sqlconn.dispose ();    }

　

In addition, when the DTS Package Script component Input output items, the new output column, inserted into the target column, if the script does not assign the output column, the output column will have a default value

The default value for DateTime is 0000-00-00

string defaults to an empty string (NOT NULL)

Then, to make the target of the database inserted null, to assign values to the input input columns,

For example:

true;

Supplement 2: When executing a stored procedure, use a datetime type parameter to pass in to OLE DB because the set type is datetime, so there is a default value,

This may be a bug, this value will not change, even if the outside changes, passed into the stored procedure, it is also the original initial value.

The solution to this problem is to make the field type even a string, not a datetime, and then set the variable to a string expression

For example, when updating the current time to run the system, use the system time, string function to convert

SSSI, there's a lot of holes in it.

[SSIS] In the script using database connection string to query and other processing, into the pit