I've got the June CTP Sql 2005 but SSIS doesn't work

"Beta period is over".

I wonder if I'll be able to call any SSIS programatically. Database Engines works fine, as usual.

I meant from BIDS I can't open any SSIS|||

Have you uninstalled the CTP and installed RTM?

What error are you getting?

sql

It's possibel run SSIS in SQL Server 2005 Express Edition

Hy,

I wont now if it's possible to run SSIS in my server client that have instaled SQL Server 2005 Express Edition?

Regards,

Filipe Silva

Hello,

SSIS comes only with Microsoft SQL Server, it's not part odf the Express Edition

Regards,
Ovidiu Burlacu

|||

No. SQL Server Express does not include SSIS.

-Jamie

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.

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 for "embedded" SSIS solutions

My warehouse app employs a distributed architecture. Extractions from disparate (wildly so) systems, and transformations and loads into a standardized schema are performed at various locations close to the source systems (both physically and "logically" speaking). There are security and other reasons for this. However this causes some related design and implementation challenges for the ETL processing.

For one, the ETL processes must be successfully operated by non-technical medical administrators, who actually have little interest in the application and sometimes even the analytics produced by the system, who have other more pressing day to day work they want to be doing, in organizations where turnover is high,training is spotty, and LANs are fragile and often congested.

So, real-time feedback to the operator during processing is pretty dern important. I have built a fairly sophisticated GUI (using .Net forms inside a script component) for the operational interface-input boxes just wouldn't cut it).

But that interface is lacking real time feedback as to processing progress at runtime.

Anyone got that T-shirt yet? I'm thinking I need progress bars and real-time task and component progress reports. Also. is there a way to capture the built in logging output in real-time?

Wow. Interesting stuff if you can make that happen. I'm fairly sure no-one had attempted it yet.

The only way i can see it being "real-time" is if you were to monitor the logging sink (i.e. table/event log/file) and make it appear in your interface. Perhaps you could put it ito a SQL Service Broker queue and consume it with your app.

Interested to hear more about this if and when you achieve it.

-Jamie

|||

I believe a custom log provider will give you real time access to all generated events, allowing you to for instance post events to your GUI.

Cheers/Kristian

|||OK, that will be my first research approach. Thanks!|||

Thanks Jamie, I will be researching log sink and custom log components for the next few days I guess.

p.s. Thanks for your SSIS blog. It has been a big help to me!

Issue XMLA queries via SSIS

Hello,

We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use...

More info regarding our processes:

OUR SSIS flow is designed to:

1.) backup an existing cube

2.) create a new cube leaving the original in place for users to use while the new one is building

3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly built cube back to the original cube's name

We used DDL code files (DDL Task Objects within SSIS) which contain the XMLA query code for each CREATE, DELETE, ALTER statements used for each specific task:

The flow:

1.) BACKUP Original_Cube

2.) CREATE New_Cube

3.) PROCESS New Cube

4.) DELETE Original Cube

5.) ALTER New_Cube to Original Cube

In order to execute each DDL Task code set for a specific task, it is necessary to specify a connection to a given catalogue... however, in our case, and in the general case of using CREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available...

it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow)

Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this....

the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...?

THANK YOU!

Michael

Hi Michael,

I'm hoping there is an easy answer to this. The "Execute DDL Task" should do all you need to do. Anything you can do with XMLA can be done with that task.

I've got alot of XMLA material here: http://blogs.conchango.com/jamiethomson/archive/tags/XMLA/default.aspx and alot of that talks about issuing XMLA from SSIS. Particularly this one:

Process SSAS dimensions and measure groups individually

(http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx)

-Jamie

Issue with the ForEachLoop Task in SSIS

Hi,

I am using a SQL task to execute a stored procedure which returns a single field with multiple records. I want the records returned by the stored procedure to be processed one by one within a ForEachLoop container. How do I assign the records one by one to one variable and use it in a Script task running inside the ForEachLoop container.

I am using 2 tasks in my package.

In my first task I call a SQL task that executes a stored procedure which returns a list of reference numbers (TrackData). This works perfectly.

However, in my second task I must use the ForEachLoop task to loop through the above list and set the value of var_TrackData (a user variable declared by me) with the value of the TrackData present in it during that particular loop.

I am not sure how to go about the second task. Any help would be greatly appreciated.

Create an object variable.

In the execute sql task set the resultset to the variable name (set result name to 0).

Create a variable to hold the reference numbers (int or string?)

Create a for each loop task

Set the collection to for each ADO enumerator

Set the ADO object source variable to your object variable name (which contains the resultset).

In variable mappings set the variable to the variable name you created to hold the reference numbers

Set the precednce so that the execute sql runs before the for each llop.

Now when this runs the object variable will be set to the resultset the the for each loop will itterate through it setting the variable to the reference number for each loop.

|||

See if this post gives you some idea of how you can do it:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

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 File System Task and folder locking

I'm having an issue where a folder that I'm accessing in an SSIS package gets locked up and cannot be deleted by my package later in the process.

1) I use a File System task to rename the folder. At this point, it appears to become locked and cannot be deleted.

2) Later I attempt to delete the folder using another File System task object. I get the error '[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process."'.

My package accesses the folder via a drive mapping. This seems to be part of the problem, as it's possible to delete the folder directly but not via the drive mapping. Even once the package completes it's still not possible to delete the folder in Windows Explorer via the drive mapping.

I've since rebooted my machine and re-created the network drive mapping and the problem has gone away.

Issue with Dynamic SQL stored procedure and SSIS OLE DB Data Reader

We are writing SSIS packages to pull data from MSO’s through a stored procedure and drop the output into the pipeline to store it on our local database.

Here is an example of a very simple data flow task that executes the following query in step 1

EXEC GetCustomerData @.SELECT = 'OrganizationID', @.FROM = 'Organization',@.WHERE = 'StateOrProvinceCode = ''WA'''

This command is stored in a variable called SQLOrgCommand.

When I open the task and click on Columns I do not see any columns returned

This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.

While profiling we realized that it executed the following SQL:

SET FMTONLY ON

to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100

Invalid length parameter passed to the SUBSTRING function.

Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101

Invalid length parameter passed to the RIGHT function.

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116

Invalid length parameter passed to the SUBSTRING function.

Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117

Invalid length parameter passed to the RIGHT function.

Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187

Invalid length parameter passed to the SUBSTRING function.

The same stored procedure executed without FMTONLY OFF generates the proper output.

Is this a known bug in SQL Server? If so is there a patch that we can install.

Can you post an example of the stored proc you're using? I created a dynamic sql sproc and it seems to work just fine.

Issue with checkpoints and Event handlers

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks.The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made.Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed.OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

Rajesh Sridharan wrote:

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

This is a complicated scenario but I think this is as expected. The only tasks for which the checkpoint is written to are those in the control-flow. In scenario 1 DFT1 has not completed therefore execution will start from there on the second execution. In scenario 2 DFT2 HAS completed therfore execution will start from DFT2 on the second execution.

Tasks in the eventhandler do not affect checkpoint files, now should they. If you have "things" happening in your package that are critical to the execution of the package then they should be in the control-flow.

-Jamie

|||

Thank you, Jamie. Appreciate your response.

Rajesh

ISSUE WHILE CONNECTING TO ORACLE SOURCE WITH 64 BIT processor SQL SERVER SSIS

Hello All,

I have a unique problem while connecting to oracle source with a 64 bit processor. I can connect to the oracle from the command prompt in the 64 bit processor but not from SSIS.

The acutal problem is, when check the properties of the connection manager and provide a provider for oracle, and then provide username and password and click on test connection. I get the following error:

"Test Connection failed because of an error in initializing provider.ORA-06413: Connection not open"

Regards,

Raju

Hello All,

The above package is running fine with sql server 32 bit development but not running in 64 bit production server.

The oracle client that has been installed on the production server is a 32 bit, because our source database is an oracle 32 bit not 64 bit which is residing in another server.

Hope this additional information will help you people get it resolved.

Regards,

Raju

|||

Hi Raju,

If you want to connect to Oracle using a 32bit driver, then you should execute the SSIS package using the 32bit version of DTEXEC.

Regards,

Christian

Issue doing Integrated services programming using Microsoft.SqlServer.ManagedDTS.dll

Hi,

I am writing an installer code in C# to deploy the SSIS package.

I want to use Microsoft.SqlServer.ManagedDTS.dll for it.

It is mentioned in few articles available online that Microsoft.SqlServer.ManagedDTS.dll ships with SQL Server 2005.

I searched on our database server but could not get it.

Anyone having idea on this please help.

HV

Don't try and install SSIS by hand, it is not a good idea It is not supposed to be a redistributable component. There are a lot more assemblies that just that one for SSIS.

Since SSIS requires a full SQL Server license, why not use the regular SQL setup to do this for you. You can choose which servers you want.

If you have installed SSIS on your server then it will certainly be in the GAC, but it may not be on the file system outside of this, unless you installed the tools, in which case it is - C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

|||Another question to ask: Is SSIS installed on this SQL Server box? A lot of SQL 2005 servers will be set up by their DBAs to not have any unnecessary components installed, and SSIS sometimes falls into this category.|||

Hi,

Thank you Darren.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but dll is not available.

Which tool installation delivers Microsoft.SqlServer.ManagedDts.dll?

Please let me know.

HV

|||

Hi,

Thank you Mathew.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but it's not available.

HV

|||

Hi,

I picked the Microsoft.SQLServer.ManagedDTS.dll from following folder:

C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91>

Similarly picked Microsoft.SqlServer.DTSRuntimeWrap.dll also.

I added it as reference in my .NET application.

When I execute the program I get below error:

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Any Clue?

How to get the RunningPackages information back to a client PC?

HV

|||

If you want running packages information, why not just use the MS tools?

Do you have SSIS tools installed on the local machine that hosts the program? The error indicates that Microsoft.SqlServer.DTSRuntimeWrap is not installed on the local PC?

|||

No SSIS is not installed on the machine which hosts program.

But that is my requirement , I want to deploy SSIS package remotely.

I have installed Microsoft.SQLServer.DTSRuntimeWrap.dll in GAC.

Let me know if there is a way out to use it on a machine where SSIS is not installed.

HV

|||

SSIS is not installed you say, and you get an error that says in cannot find a COM component. Do you think there may be a connection?

I refer you to my original post, apart from saying that a manual install was a silly idea, I also pointed out "There are a lot more assemblies that just that one for SSIS."

As a start point that DLL is just a wrapper to the COM library that does the work, the name hints at that, and the error proves that it is trying to use a COM DLL that is not there, a COM DLL with a ProgID of {E44847F1-FD8C-4251-B5DA-B04BB22E236E} perhaps. As I said before there are lots of DLLs involved in SSIS not just one, so use a proper install.

What you are asking for a not a supported scenario, you may be violating your license agreements if not careful, and at any rate will be a very complicated task to try and reverse engineer the requirements, and very slow if you cannot track a missing COM DLL down yourself.

If you think this is wrong, post feedback to MS (http://connect.microsoft.com) telling them why you think you should have some redistribuatable support, but in the mean-time you will need to run one of the MS installs to get this to work.

Why do you not want to use a MS install?

issue

i have a data source which is a weblogs in which data is stored in ncsa format using ssis i need to get that data to one of my database tables

can u guys plz help me out in this.

i.e to to build the ssis scenario

thanks

praneeth

sample weblog

10.196.2.236 - wromero@.kpmg.com [26/Jun/2007:12:04:44 -0400] "POST /krisk3/krisk3/(ea12r4f4nin0ta55h0suj445)/Evaluation/Evaluation.aspx HTTP/1.0" 200 42925

You could use a script source to read the file in line by line and parse it into columns. Are the lines formatted consistently?

|||

I would be inclined to import the whole lot into a single-column dataset and then parse it using derived column steps.

This would work fine if your line length is relatively predictable (by which I mean you have no long text blocks in the entries) and you have a consistant end-of-line (no CR/LF in the middle of a line.)

If your data is more complex, take a look at using a script step to parse each line.

|||

Check the Fixed Width text file option. You can retrieve the columns as required. You can even pictorially see the reperesentaion in SSIS of your data.

Virendra