0

I have an SSIS package between a SQL2000 and SQL20008 server that cannot connect since migrating to VM. The SSIS package runs a data transfer between the two servers using 3 Connection manager strings. I am trying to understand what the values of the connection strings mean. I am not a SQL DB admin/tech at all. The 3 strings are below.

Some of the values seem self-explanatory, others I do not understand.

Data Source=1.1.1.1;
User ID=SQLadm;
Initial Catalog=SQL_db;
Provider=SQLNCLI10.1;
Persist Security Info=True;
OLE DB Services=-13;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}1.1.1.1.SQL_db.SQLadm;


Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E92CF7CB-23A5-4713-8593-F2FF92C980EA}localhost.Server2008;


Data Source=localhost;
Initial Catalog=Server2008;
Provider=SQLNCLI10.1;
Integrated Security=SSPI;
Connect Timeout=10;
Auto Translate=False;
Application Name=SSIS-TransferPkg-{E28D4B65-CE56-4BD7-BEC4-02EE1738F568}localhost.Server2008.SQLadm;

Specifically what do the following mean: Provider= OLE DB Services= yes, object linking services and I read the MS Doc here( https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms717922(v=vs.85)#:~:text=OLE%20DB%20provides%20common%20services,performance%20of%20OLE%20DB%20providers.&text=(OLE%20DB%20services%20cannot%20be,see%20Creating%20Data%20Source%20Objects.) but hoping someone can dumb it down for me.

Also what do the 32 digit codes in {} mean? Is it a hash check/authentication or ID for some process?

And for Application Name= this seems redundant. I am already running the transfer package, why does the name and the connection parameters (IP.DB.USER) need to be repeated at the end?

Thank you for any input or resources I can reference! I am not a DB/SQL admin in any fashion.

1

the provider is the driver name. A connection can be opened against variuos sources and destination (SQL server, Oracle etc..). You have to specify the proper driver.

SQLNCLI10.1 is the sql server native client version 10.1

OLE DB Services=-13; is the default option for additional features like pooling and recursion etc...

Application Name is only a string that can be usefull to distinguish the running workload from other applications. Most of times you can find ".NET application" or the ssis dtsx name or id like in this case.

2
  • Nice. thank you. How about that long hex value? Is that a hash that checks between both servers, or a process ID, or a job ID or something else?
    – mcv110
    Apr 28 at 13:20
  • 2
    if you are referring on this {E28D4B65-CE56-4BD7-BEC4-02EE1738F568} it is a guid that identify the dts package.it is inside application name so it does not have any relevant effect in transactions. It is only a label.
    – MBuschi
    Apr 28 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.