password lost

Hello,

I import a package into Integration Services (it containt 3 ole db connections to SQL Server), then I schedule this into a job.

When I run the job, it failed !

For the connections, I use a sql user and, in the step that run package, I notice that the password isn't store

I add password in the connection string ("Password=password;") and when I rerun the job, it terminate with success.

My question is: Is it possible to store the password directly into the package and so avoid password encoding several times ?

Regards

Arnaud

Arnaud H wrote:

Hello,

I import a package into Integration Services (it containt 3 ole db connections to SQL Server), then I schedule this into a job.

When I run the job, it failed !

For the connections, I use a sql user and, in the step that run package, I notice that the password isn't store

I add password in the connection string ("Password=password;") and when I rerun the job, it terminate with success.

My question is: Is it possible to store the password directly into the package and so avoid password encoding several times ?

Regards

Arnaud

Sorry, I forgot saying you that the package come from a DTS package. It has been build with the DTS migration tool.

Regards

Arnaud

Passing system variables to stored procedure

How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @.MachineName, @.TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...

Thanks for the help.

In the execute sql task refer to parameters with question marks. Then on the parameter mapping page of the execute sql task you can map the question marks to variables.

|||

Thanx...I need the same for the OLE DB transformation

|||

Do you mean OLE DB source? It's pretty much the same as the execute sql task.

Put question mark's where you want the variable then click the 'parameters' button and choose the variable that you want to use for it.

If your source provider (DB2) doesn't allow you to pass parameters you'll have to create a variable that builds the sql statement referencing another variable. Then change the 'data access mode' in the ole db source to 'sql command from variable'.

Good luck.

|||

I mean OLE DB Command Transformation...

|||

I think the answer to your original question you want is yes, use the Derived Column to make a variable into a column so that you can use it, and question marks are the placeholders.

|||

Audit transformation can also be used (I need to populate log table).

Passing system variables to stored procedure

How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @.MachineName, @.TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...

Thanks for the help.

In the execute sql task refer to parameters with question marks. Then on the parameter mapping page of the execute sql task you can map the question marks to variables.

|||

Thanx...I need the same for the OLE DB transformation

|||

Do you mean OLE DB source? It's pretty much the same as the execute sql task.

Put question mark's where you want the variable then click the 'parameters' button and choose the variable that you want to use for it.

If your source provider (DB2) doesn't allow you to pass parameters you'll have to create a variable that builds the sql statement referencing another variable. Then change the 'data access mode' in the ole db source to 'sql command from variable'.

Good luck.

|||

I mean OLE DB Command Transformation...

|||

I think the answer to your original question you want is yes, use the Derived Column to make a variable into a column so that you can use it, and question marks are the placeholders.

|||

Audit transformation can also be used (I need to populate log table).