J# using JDBC for SQL Server

code:

/* Establish a test connection to remote SQLServer in J# using JDBC */
package JSharpConnTest;
import java.sql.*;
public class JSharpConnTest
{
private static ResultSet rs;
private static Connection conn = null;
private static Statement stmt = null;
private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
private static final String sServerName = "CLUSTERTEST";
private static final String sPortNumber = "1433";
private static final String sDBName = "ClientLetterWorkRequests";
private static final String sUserName = "sa";
private static final String sPassword = "1111";
private static final String sSQLQuery = "SELECT * FROM CLDatabases";
private static final String sURL = sSubprotocol + sServerName +
":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
public static void main(String[] args)
{
getConnection();
}
public static void getConnection()
{
System.out.println( "Connecting to.. " + sURL );
try
{
//Register the driver
// Microsoft SQL Server 2000 Driver for JDBC
// --problem locating driver?
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// Pass connection URL
conn = DriverManager.getConnection(sURL, sUserName, sPassword);
// Query database
stmt = conn.createStatement();
rs = stmt.executeQuery(sSQLQuery);
// Test data retrieval
while (rs.next());
{
String colOne = rs.getString("DATABASEKEY");
String colTwo = rs.getString("SERVERTYPE");
System.out.println(colOne + " " + colTwo);
}
}
catch (java.lang.ClassNotFoundException ex)
{
System.err.println("\nClassNotFoundException: " + ex.getMessage());
}
catch (SQLException ex)
{
System.err.println("\nSQLException: " + ex.getMessage());
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
finally
{
// Release resources
try
{
if (conn != null)
conn.close();
if (stmt != null)
stmt.close();
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
}
} // end getConnection()
} // end JSharpConnTest

This statement:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Causes the following exception:
[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDriver"}java.lang.ClassNotFoundExcep
tion
I installed Microsoft SQL Server 2000 Driver for JDBC
I believe the problem is with locating the driver, unlike Java, J# doesn't
use classpath enviromental variable. I am not sure how to add the driver
reference in J#.Hi
Why use JDBC when ADO.NET can do everything for you?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RobertStout" <
RobertStout@.discussions.microsoft.com>
wrote in message
news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
>

code:

>
 /* Establish a test connection to remote SQLServer in J# using JDBC */
>
>
 package JSharpConnTest;
>
 import java.sql.*;
>
>
 public class JSharpConnTest
>
 {
>
 private static ResultSet rs;
>
 private static Connection conn = null;
>
 private static Statement stmt = null;
>
 private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
>
 private static final String sServerName = "CLUSTERTEST";
>
 private static final String sPortNumber = "1433";
>
 private static final String sDBName = "ClientLetterWorkRequests";
>
 private static final String sUserName = "sa";
>
 private static final String sPassword = "1111";
>
 private static final String sSQLQuery = "SELECT * FROM CLDatabases";
>
 private static final String sURL = sSubprotocol + sServerName +
>
 ":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
>
>
 public static void main(String[] args)
>
 {
>
 getConnection();
>
 }
>
>
 public static void getConnection()
>
 {
>
 System.out.println( "Connecting to.. " + sURL );
>
>
 try
>
 {
>
 //Register the driver
>
 // Microsoft SQL Server 2000 Driver for JDBC
>
 // --problem locating driver?
>
 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
 // Pass connection URL
>
 conn = DriverManager.getConnection(sURL, sUserName, sPassword);
>
>
 // Query database
>
 stmt = conn.createStatement();
>
 rs = stmt.executeQuery(sSQLQuery);
>
>
 // Test data retrieval
>
 while (rs.next());
>
 {
>
 String colOne = rs.getString("DATABASEKEY");
>
 String colTwo = rs.getString("SERVERTYPE");
>
 System.out.println(colOne + " " + colTwo);
>
 }
>
 }
>
 catch (java.lang.ClassNotFoundException ex)
>
 {
>
 System.err.println("\nClassNotFoundException: " + ex.getMessage());
>
 }
>
 catch (SQLException ex)
>
 {
>
 System.err.println("\nSQLException: " + ex.getMessage());
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 finally
>
 {
>
 // Release resources
>
 try
>
 {
>
 if (conn != null)
>
 conn.close();
>
 if (stmt != null)
>
 stmt.close();
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 }
>
>
>
 } // end getConnection()
>
 } // end JSharpConnTest
>

>
>
This statement:
>
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
Causes the following exception:
>

[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDr
iver"}java.lang.ClassNotFoundException
>
>
I installed Microsoft SQL Server 2000 Driver for JDBC
>
I believe the problem is with locating the driver, unlike Java, J# doesn't
>
use classpath enviromental variable. I am not sure how to add the driver
>
reference in J#.|||If I could use ADO.NET I would of been done with the entire app a long time
ago.
Sadly, I have to use JDBC.
Can you help?
Thanks
-Rob
I would much rather use ADO.NET but I can't, I have to use JDBC.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Why use JDBC when ADO.NET can do everything for you?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RobertStout" <RobertStout@.discussions.microsoft.com> wrote in message
> news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
> [java.lang.ClassNotFoundException]{"com.microsoft.jdbc.sqlserver.SQLServerDr
> iver"}java.lang.ClassNotFoundException
>
>sql

Its very slow to exec a IS package by DTExecUI

Its very slow to exec an IS package by DTExecUI,because its Package Execution Progress waste too much cpu resource,how to solve this, Thanks

I have found its run faster if using start without debugging in SSISsql

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

It must have been a corruption

I cannot take this anymore...

I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...

How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'

This is absolutely pathetic from Microsoft to release such a bug-ridden system.
Have you setup correct login information on the connections?

What is the ProtectionLevel property of the package set to?|||

mj van den berg wrote:

I cannot take this anymore...

I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...

How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'

This is absolutely pathetic from Microsoft to release such a bug-ridden system.

Many many other people have managed it (including your colleague) thus proving that the product is not bug-ridden. Perhaps you might want to look a bit closer to home before blaming anyone else.

Exactly where are you trying to apply the connection string? You are aware that under some circumstances SSIS will not store the password for you, right? That's is why Phil asked about ProtectionLevel

-Jamie

|||

Check if you have the same user profile of your collegues!

Regards,

Pedro

|||

PedroCGD wrote:

Check if you have the same user profile of your collegues!

Regards,

Pedro

He won't likely.|||

If your problem is in your company, check in active directory if you have the same groups of your collegues...

|||

PedroCGD wrote:

Check if you have the same user profile of your collegues!

Regards,

Pedro

I have checked that. We are using the same user profiles. Both packages are set to EncryptSensitiveWithUserKey.

The thing is: I have both projects on my pc and my colleague and I are both trying to fix this and even though his works, none of us can explain why the thing fails since there are 2 of us who have confirmed it being the same. I am sorry if I sound like I am passing the blame to microsoft and will admit that I am new in SSIS (obviously) but the reason I'm so frustrated is that this is the first software package i have worked on in a long time where one fixes things by recreating the component or even the package from scratch and things will start to work.

I mean, every time I, for instance, delete a connection manager, the environment does not remove the component's ID from the dtsx file and I have to manually do it otherwise I get the message of the project being corrupt. This is an ongoing thing that I am experiencing and it's getting frustrating.

I have now recreated the package from scratch and all of a sudden some things are starting to work such as directory variables that point the 'for each loop' to a folder where flat files are to be processed....and I did not change anything compared to the previous package.

About the connection string, I have created an oledb connection manager and set the connection information there. It was tested and it connected. Then I defined it in a script's connection manager which i use in the script to execute a simple query which never fire's since I cant get the connectionstring to work...again, this is the same as the colleagues package.

I am sorry for sounding a tad over-frustrated yesterday...today will be better since my aircon is working again

Regards
|||You may have already tested this, but if you are using EncryptSensitiveWithUserKey, only the user account that created the package will be able to decrypt passwords in it. So unless you and your colleague are logging on to the network with the same user ID, you shouldn't expect to run his packages or vice versa.|||Hi,

Thanks for the reply. We have created a user for this and both use that user.

I have spent a great number of hours recreating the project on a different machine, that I stole from our store room, using the same methods etc and it works!

It must be something that had gone corrupt on my machine which is strange since it is a brand new laptop. Our internal it guys set it up though including the development software so I'm going to do it myself now.

Thanks for the support.|||And you both log into the machine with the same user account on the same machine? If not the same machine, I believe the user key will be different and hence, your problem.|||

mj van den berg wrote:

We have created a user for this and both use that user.

Hi Mj Van Den Berg,

I'm curious about this. What type of user did you create? Is this a SQL Server login or an Active Directory account?

Thanks,

Andy

|||I'm thinking the same as Andy here (I think ).
The "userkey" points to the user you use to log in to windows, not the user to log in on the DB. So if your colleague made a package and you used it, this would mean you, on your system, would not be able to decrypt the connection info for the datasource.
|||Hi there,

The user account is a sql server account, which is why we could log on again after recreating it on the other machine.

I have played around setting the package security to almost every option (even those I knew wouldn't work) but it went bad.

I finally decided to rebuild my machine when my colleague told me that his environment wouldn't go corrupt when he, for instance deletes an existing data connection-manager and that he rarely had to manually edit the dtsx file and another non SSIS project of mine started to act up with it's designer not functioning properly as well.

Regards
|||

Hi Mj Van Den Berg,

Rebuilding your laptop may or may not correct the issue. I hope it works for you.

To be sure I'm understanding the scenario:

1. You and your colleague both build SSIS packages.

2. Your colleague's SSIS packages execute - even when you log into the domain as yourself from your laptop and execute your colleague's SSIS packages manually.

3. SSIS packages you author execute partially (you can get some connections to work now but cannot connect from a script task).

Please clarify.

Here's some of my thinking:

1. Connectivity in SSIS is no simple matter. Understanding connectivity is one of the steeper ledges on the SSIS learning curve. There are multiple moving parts: Data Connections are stored on the development workstation; Connection Managers are managed at either the solution or package level, depending on how they're defined; Source and Destination Adapters can be stand-alone objects in a Data Flow or integrated into Data Flow components or Control Flow Tasks.

2. SSIS Security is an equally steep ledge on the SSIS learning curve. Information considered "Sensitive" is never maintained as clear text. This means if you:

a. define an OLE DB Connection Manager using the sa account and proper password,

b. check the "Save my password" checkbox,

c. test the connection (and the test is successful),

d. close the Connection Manager editor,

e. browse to the Connection Manager Properties,

f. highlight and copy the ConnectionString value, and

g. paste this value into the ConnectionString property of an ADO.Net connection you are establishing from a Script Task;

the ADO.Net connection inside the Script Task will always fail because it is missing the password property - while the Source and Destination Adapters that reference the OLE DB Connection Manager will successfully connect. This is all by design. I'm not saying this is what you did but if you did, all you have to do to make this ADO.Net connection work inside the Script Task is simply append text like "Password=[MySaAccountPassword];" to the ADO.Net ConnectionString property inside the Script Task.

3. SSIS connectivity and security interact. The behavior is controlled largely by the ProtectionLevel property of the package but it's important to realize that, in an Active Directory domain, your Active Directory user profile can play an important role in package execution - depending on how you define connectivity and security in your SSIS solution and package. The default ProtectionLevel property setting is EncryptSensitiveWithUserKey. This setting means your Active Directory credentials are used to determine your rights to connect to any data source (SQL Server, directory, file) outside the package - unless you explicitly provide security information (username \ password). If your Active Directory user profile is identical (all SQL Server, directory, file permissions are the same) to that of your colleague, it's likely your laptop rebuild will correct some of the issues you're experiencing. If your user profiles differ, rebuilding the laptop may not correct the issues.

From your three posts above, I understand you don't like the fact you cannot delete connection managers completely from your SSIS package by simply clicking on them and pressing the Delete key. This issue can be the result of:

1. Corrupt package metadata,

2. A less-than-up-to-date installation of SSIS (SP2 is current),

3. An incorrect or corrupt installation of SSIS.

It's important to consider all of these scenarios when troubleshooting these types of issues. I advise considering them in the order listed.

In my opinion, it's better to encounter and address these issues now. Most folks learn about them on deployment day which is far more painful than during development.

Andy

|||Hi Andy,

Thank you very much for the in-depth and helpful post.

I have rebuilt my laptop and updated every software package that I could think of and, thank goodness, it works!

I'm sure that I must have had a corrupted installation of SSIS or even SQL server 2005 otherwise the problem would have persisted. For informative purposes, I want to elaborate that we did use a password variable which is passed through to the script which is used to take care of the problem of it failing due to the password property.

I am still new to the SSIS package and I should have known better to assume that because it is fairly new that it does behave as strangely as it did on my pc. There was also some strange behavior in SQL server's management studio bombing out frequently but I only picked this up after my issues with SSIS.

Thanks for all the help
Regards
Marcel

It must have been a corruption

I cannot take this anymore...

I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...

How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'

This is absolutely pathetic from Microsoft to release such a bug-ridden system.
Have you setup correct login information on the connections?

What is the ProtectionLevel property of the package set to?|||

mj van den berg wrote:

I cannot take this anymore...

I have installed sql server service pack 1 which is the same as a colleague of mine has and his package works...

How in name of goodness are we supposed to set a connectionstring that actually works...I have tried recreating the project and deleting a recreating the components but the message is still: Login failed for user 'sa'

This is absolutely pathetic from Microsoft to release such a bug-ridden system.

Many many other people have managed it (including your colleague) thus proving that the product is not bug-ridden. Perhaps you might want to look a bit closer to home before blaming anyone else.

Exactly where are you trying to apply the connection string? You are aware that under some circumstances SSIS will not store the password for you, right? That's is why Phil asked about ProtectionLevel

-Jamie

|||

Check if you have the same user profile of your collegues!

Regards,

Pedro

|||

PedroCGD wrote:

Check if you have the same user profile of your collegues!

Regards,

Pedro

He won't likely.|||

If your problem is in your company, check in active directory if you have the same groups of your collegues...

|||

PedroCGD wrote:

Check if you have the same user profile of your collegues!

Regards,

Pedro

I have checked that. We are using the same user profiles. Both packages are set to EncryptSensitiveWithUserKey.

The thing is: I have both projects on my pc and my colleague and I are both trying to fix this and even though his works, none of us can explain why the thing fails since there are 2 of us who have confirmed it being the same. I am sorry if I sound like I am passing the blame to microsoft and will admit that I am new in SSIS (obviously) but the reason I'm so frustrated is that this is the first software package i have worked on in a long time where one fixes things by recreating the component or even the package from scratch and things will start to work.

I mean, every time I, for instance, delete a connection manager, the environment does not remove the component's ID from the dtsx file and I have to manually do it otherwise I get the message of the project being corrupt. This is an ongoing thing that I am experiencing and it's getting frustrating.

I have now recreated the package from scratch and all of a sudden some things are starting to work such as directory variables that point the 'for each loop' to a folder where flat files are to be processed....and I did not change anything compared to the previous package.

About the connection string, I have created an oledb connection manager and set the connection information there. It was tested and it connected. Then I defined it in a script's connection manager which i use in the script to execute a simple query which never fire's since I cant get the connectionstring to work...again, this is the same as the colleagues package.

I am sorry for sounding a tad over-frustrated yesterday...today will be better since my aircon is working again

Regards
|||You may have already tested this, but if you are using EncryptSensitiveWithUserKey, only the user account that created the package will be able to decrypt passwords in it. So unless you and your colleague are logging on to the network with the same user ID, you shouldn't expect to run his packages or vice versa.|||Hi,

Thanks for the reply. We have created a user for this and both use that user.

I have spent a great number of hours recreating the project on a different machine, that I stole from our store room, using the same methods etc and it works!

It must be something that had gone corrupt on my machine which is strange since it is a brand new laptop. Our internal it guys set it up though including the development software so I'm going to do it myself now.

Thanks for the support.|||And you both log into the machine with the same user account on the same machine? If not the same machine, I believe the user key will be different and hence, your problem.|||

mj van den berg wrote:

We have created a user for this and both use that user.

Hi Mj Van Den Berg,

I'm curious about this. What type of user did you create? Is this a SQL Server login or an Active Directory account?

Thanks,

Andy

|||I'm thinking the same as Andy here (I think ).
The "userkey" points to the user you use to log in to windows, not the user to log in on the DB. So if your colleague made a package and you used it, this would mean you, on your system, would not be able to decrypt the connection info for the datasource.
|||Hi there,

The user account is a sql server account, which is why we could log on again after recreating it on the other machine.

I have played around setting the package security to almost every option (even those I knew wouldn't work) but it went bad.

I finally decided to rebuild my machine when my colleague told me that his environment wouldn't go corrupt when he, for instance deletes an existing data connection-manager and that he rarely had to manually edit the dtsx file and another non SSIS project of mine started to act up with it's designer not functioning properly as well.

Regards
|||

Hi Mj Van Den Berg,

Rebuilding your laptop may or may not correct the issue. I hope it works for you.

To be sure I'm understanding the scenario:

1. You and your colleague both build SSIS packages.

2. Your colleague's SSIS packages execute - even when you log into the domain as yourself from your laptop and execute your colleague's SSIS packages manually.

3. SSIS packages you author execute partially (you can get some connections to work now but cannot connect from a script task).

Please clarify.

Here's some of my thinking:

1. Connectivity in SSIS is no simple matter. Understanding connectivity is one of the steeper ledges on the SSIS learning curve. There are multiple moving parts: Data Connections are stored on the development workstation; Connection Managers are managed at either the solution or package level, depending on how they're defined; Source and Destination Adapters can be stand-alone objects in a Data Flow or integrated into Data Flow components or Control Flow Tasks.

2. SSIS Security is an equally steep ledge on the SSIS learning curve. Information considered "Sensitive" is never maintained as clear text. This means if you:

a. define an OLE DB Connection Manager using the sa account and proper password,

b. check the "Save my password" checkbox,

c. test the connection (and the test is successful),

d. close the Connection Manager editor,

e. browse to the Connection Manager Properties,

f. highlight and copy the ConnectionString value, and

g. paste this value into the ConnectionString property of an ADO.Net connection you are establishing from a Script Task;

the ADO.Net connection inside the Script Task will always fail because it is missing the password property - while the Source and Destination Adapters that reference the OLE DB Connection Manager will successfully connect. This is all by design. I'm not saying this is what you did but if you did, all you have to do to make this ADO.Net connection work inside the Script Task is simply append text like "Password=[MySaAccountPassword];" to the ADO.Net ConnectionString property inside the Script Task.

3. SSIS connectivity and security interact. The behavior is controlled largely by the ProtectionLevel property of the package but it's important to realize that, in an Active Directory domain, your Active Directory user profile can play an important role in package execution - depending on how you define connectivity and security in your SSIS solution and package. The default ProtectionLevel property setting is EncryptSensitiveWithUserKey. This setting means your Active Directory credentials are used to determine your rights to connect to any data source (SQL Server, directory, file) outside the package - unless you explicitly provide security information (username \ password). If your Active Directory user profile is identical (all SQL Server, directory, file permissions are the same) to that of your colleague, it's likely your laptop rebuild will correct some of the issues you're experiencing. If your user profiles differ, rebuilding the laptop may not correct the issues.

From your three posts above, I understand you don't like the fact you cannot delete connection managers completely from your SSIS package by simply clicking on them and pressing the Delete key. This issue can be the result of:

1. Corrupt package metadata,

2. A less-than-up-to-date installation of SSIS (SP2 is current),

3. An incorrect or corrupt installation of SSIS.

It's important to consider all of these scenarios when troubleshooting these types of issues. I advise considering them in the order listed.

In my opinion, it's better to encounter and address these issues now. Most folks learn about them on deployment day which is far more painful than during development.

Andy

|||Hi Andy,

Thank you very much for the in-depth and helpful post.

I have rebuilt my laptop and updated every software package that I could think of and, thank goodness, it works!

I'm sure that I must have had a corrupted installation of SSIS or even SQL server 2005 otherwise the problem would have persisted. For informative purposes, I want to elaborate that we did use a password variable which is passed through to the script which is used to take care of the problem of it failing due to the password property.

I am still new to the SSIS package and I should have known better to assume that because it is fairly new that it does behave as strangely as it did on my pc. There was also some strange behavior in SQL server's management studio bombing out frequently but I only picked this up after my issues with SSIS.

Thanks for all the help
Regards
Marcel

Issues with Logging option in SSIS

Hi,

I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.

Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.

Vivek S

Does SSIS know to look for the loggingmode value, though? That is, in the package configurations, you have a SQL Server based configuration set to look for the correct filter that contains:

Code Snippet

<Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration>

|||

Hi Phil,

The config table has the value as

Configurationfilter Configuredvalue PackagePath Configuredvaluetype

pkgABC 1 \Package.Properties[LoggingMode] Object

Regards,

Vivek

|||

Vivek S wrote:

Hi,

I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.

Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.

Vivek S

Vive, I was able to reproduce the issue. It looks like you cannot change the value of LoogingMode property via package SQL Server based configuration. The odd part is that when I used XML file configuration it worked fine.

BTW, I tested this in 9.00.1399.00 SS version. I wonder if this is a known issue or if it has been fixed on further SPs.

Vive, I would recommend you to search the SQL Server connect site to see if that issue has been reported before, if not fill in a bug report.

Can any one else validade this?

|||It kinda makes sense though. The package needs to know if it's going to log or not before reading package configurations -- after all, the package is logging already before reading package configurations. (as indicated by any package execution's log results)|||

Phil Brammer wrote:

It kinda makes sense though. The package needs to know if it's going to log or not before reading package configurations -- after all, the package is logging already before reading package configurations. (as indicated by any package execution's log results)

It does not make too much sense to me. Specially when it works fine if you use an XML file configuration, and with the other workaround described by the OP.

If would make more sense if we had a list of properties that are not meant to be configured at run time; or better yet the configuration wizard would prevent you of attempting it.

|||Well, it's all reproducible on my SP1 installation. Sigh...

Let's see what MS has to say. Perhaps they can clarify things a bit, because logging is obviously not the first thing that happens during package execution. (And I think many of us thought it was)

[Microsoft follow-up]|||

Hi,

Thanks for your suggestion. I couldnt find any item of such case in SQL server connect. so i have submitted it as a feedback.

well, the version i am using is SP1 applied.

thanks & regards

Vivek S

|||

Vivek,

Please post a link to the issue so others (including me can vote/validate it).

Does any of the SSIS folks have something to say?

Thanks

|||This is a bug and we have already fixed it in the upcoming 2008 release. All the user defined type properties (like LoggingMode, TransactionOption, etc) have the same problem when use with SQL configuration. Please contact CSS if you need a fix for SQL Server 2005.|||To be honest, the workaround (setting the package property using an expression of a configurable variable (e.g. "User::LoggingMode") worked for me and is probably perferred over a MS hotfix as long as the workaround is documented as an annotation (which I did).

Thanks for the workaround!|||I also faced this issue in SSIS and also used the workaround: a variable to store the value assigning it to the LoggingMode property using expression.

With the workaround I am facing a minor issue: running the package with the LoggingMode=2 (disabled) an empty log file still gets generated. This is not a big issue but I would prefer if this empty log file could be avoided.

Do you observe the same behavior? Any ideas on how to overcome this?

Thanks in advance.

Issues with execute package task in SSIS

Hi,

We have used an execute package task in our master package to execute a child package and we have set the execute out of process=false. This master package is running fine in 32 bit server but is failing in 64 bit server. is there any settings to be done in the server or is it the problem with the property setting(execute out of process)

Vivek S

Please provide the specific error(s) you are receiving.|||

Hi Phil,

Here is the error msg i am getting.

Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

Thanks & regards

Vivek S

|||Can you run the child package on it's own on the x64 machine?|||

A package may fail validation for many reasons. Make sure the 64 bit server have the 64 bit version of the drivers used by the connection managers. Also notice that you could force the execution of the package in 32 bit mode (via dtexec).

BTW how are you running the package? Have you tried runnig them via dtexec to see if you can get a more detailed error?

|||

Hi,

The package exection fails irrespective of whether we run using dtexec from command prompt or from integration services.

we tried both ways, still it fails.

Just a check. its the RTM version of SQL which in currently in the 64 bit m/c where the pkg is failing where as the 32 bit m/c are applied with SP1. can this patch make a difference.

Vivek S

|||

Hi Crispin,

Yes the child package executes successfully when executed saperately.

Vivek S

|||

Vivek S wrote:

Just a check. its the RTM version of SQL which in currently in the 64 bit m/c where the pkg is failing where as the 32 bit m/c are applied with SP1. can this patch make a difference.

Vivek S

Sure, it can make a difference.

|||How are you executing the child to test it, and how are you executing the master?|||First, any Excel connections? There isn't an Excel connection manager in 64 bit mode.

Second, try running the package in 32 bit mode (use the 32 bit executable) on the 64 bit server.|||

Hi,

The issue is solved post SP1 application in 64 bit m/c. Thanks to all for the suggessions given for my posting.

Regards,

Vivek S

Issues in DTS

Hi All,

i Want to use dts in vb.net.Can any one give me the solution to thus problem?

Can dts package store the data from the table ?

Rakesh

Hello. Are you referring to DTS in SQL Server 2000 or SSIS found in SQL Server 2005.

Either way, they are both applications/platforms for data movement, manipulation, work flow but they are not themselves storage applications. So you can use SSIS from SQL Sever 2005 to move an manipulate data from/to data sources (flat files, SQL tables) but SSIS itself does not store data.

I hope that helps.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with OLE DB Command while writing to DB2 database

Hi,

I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.

The same package works fine when the target is on Oracle database or a SQL Server database.

For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.

Any suggestion regarding this would be really helpful.

Thanks,

Manish

Some thoughts on this:

1. It is better to use the OLE DB Destination to insert data into a table instead of the OLE DB Command with an insert statement. You should give that a try.

2. Are you using the Microsoft OLE DB provider for DB2 from our SQL Server 2005 Feature Pack? That's the provider that's been tested with SSIS.

3. I don't understand what you mean by not able to get input columns. What are the error messages when the package fails?

|||

Yes, I am using the Microsoft DB2 drivers only. Using the OLE DB Destination, it worls only for insert. But, I wanted to simulate a scenario, where we do, both insert and update to the target table. So, we used OLE DB Command, and wrote the insert/update query.

The same works fine on Oracle and SQL Server target database. But on DB2, it doesnt work, as in the OLE DB command, we need to map the input columns to the parameters value. The drivers are not able to fetch the input data, and everytime, it insert NULL as it is not able to get the input values.

Let me know if you need any more information on the same.

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table