configure Defaultmaxbuffersize and DefaultmaxbufferRows

i want to improve the performance of my ssis...im left with last 3 bottleneks...3 huge tables ...here are the details :

table 1 :rows : 40 million +

size of each row..1 -2 KB

i had set the default max buffer to the max..100 MB and default max buffer rows to 100,000 ... considering that average row size is 1 kb.

table 2:

rows :17 million

row size : 280 bytes

again i have set the max buffer to 100 MB and default max buffer row to 300,000 .applyin the same logic..

table 3

rows: 59 million

size per row : 85 bytes..

as the row size was small...i made default max buffer row to 1000000 that'll still be less than 100 mb..which i had set as default max buffer size....

the first 2 have given a considerable speedup..but the 3rd one (as i feared) has gone dead slow...

ne ideas..and is one huge buffer better..or many small buffers...

Can you describe what transformations you're applying to the data from table 3? And what source and destination adapters you're using?|||its a oledb source and destination....the data flow tasks i mentioned (and few other similar) r called from a sequence container in control flow... the transformation is minimal...thouh there r 2 columns compared in the where clause(just 1 in others)..and i'm not sure of indexes on that as im not in control of that DB ... ya that may be a reason...but can u suggest anything apart from that..|||

I'd suggest that you try to determine the location of the bottleneck:

To check the source, try writing out the data from the source into a Raw File, and replacing the source adapter in your existing package with a Raw File Source adapter that's pointed at this file.

To check the destination, replace the existing destination adapter with an unconfigured Export Column transform.

To check the transforms, do both of the previous things.

Configure connection strings for deployment to different servers

Hi,

We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?

-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/

Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?

Thanks in advance

Hi, it almost sounds like you have 1 configuration file...that all pacakges look at regardless what server they were deployed to. Thats the only way I could explain why making a change for the live server deployment affects packages running in test environment. If you look at the where things are deployed in each environment, do you see a config file in each location and does it contain the appropriate information? I think you can accoplish the dynamic connection string. Are you using property expressions to dynamically build/set your connections. There is a system variable available in SSIS named 'MachineName'. I must admit I have not played with it in a while and you need to be aware load order of 'stuff' is important. So, as a package spins up, it loads configurations and then applies property expressions as things are executed. So, I cannot recall if the system variable is set in time a property expression to use it. Another route is use an environment variable configuration. so the package loads and pulls in the servername frrom an environment variable on the machine and then that can be use in a property expression.

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

K

|||Will give it a try. Thanks for your help.

Configure connection strings for deployment to different servers

Hi,

We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?

-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/

Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?

Thanks in advance

Hi, it almost sounds like you have 1 configuration file...that all pacakges look at regardless what server they were deployed to. Thats the only way I could explain why making a change for the live server deployment affects packages running in test environment. If you look at the where things are deployed in each environment, do you see a config file in each location and does it contain the appropriate information? I think you can accoplish the dynamic connection string. Are you using property expressions to dynamically build/set your connections. There is a system variable available in SSIS named 'MachineName'. I must admit I have not played with it in a while and you need to be aware load order of 'stuff' is important. So, as a package spins up, it loads configurations and then applies property expressions as things are executed. So, I cannot recall if the system variable is set in time a property expression to use it. Another route is use an environment variable configuration. so the package loads and pulls in the servername frrom an environment variable on the machine and then that can be use in a property expression.

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

K

|||Will give it a try. Thanks for your help.

Configuration Packages are still in Beta

I would just like to give my feedback on the configuration packages implementation in SSIS:

THIS STUFF SHOULD BE A NO BRAINER AS IT IS A COMMON REQUIREMENT ON ETL PROJECTS.

I have trolled through these forums and found that I am not alone on this issue. Note for SSIS Team: Please bring out a HOWTO on this subject that details all the little work arounds because intuitively used it does not work.

Consider this:

I develop in BIDS on my client and everything runs. I create a config package and I choose only the Name and ConnectionString Properties for each Connection Manager. (Thanks to Jamie for that tip)

I build the app and a deployment utility is created. I navigate to the deployment directory, and deploy. I point to a mapped share on the production server, change all the properties for production and continue until finished.

On the production server I run a script containing:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx >> D:\SSIS\Source\logs\Source.log

I get an error: Cannot find the configuration file: source.dtsConfig

I tried putting the following in the script:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx /CONFIGFILE D:\SSIS\Source\code\source.dtsconfig >> D:\SSIS\Source\logs\Source.log

Still: Cannot find the configuration file: source.dtsConfig

I have to MANUALLY edit the dtsx file to put the path in from the the name of the config file and THEN it works.

Thinking that it might not like a mapped share from desktop to deploy to, I copIED the contents of the depLoyMENT directory on my desktop to a folder on the production server and deploy from there. It should pick up the proper path from there. NOT!!!!

So it was back to editing the dtsx file.

So there you have it. I await all comments with great interest.

I hope this is all fixed in SP2, Because I don't want to waste anymore time on what should be a simple task.

Michael Morrissey.
ETL and BI Developer.

if i'm not mistaken, your issue cries out for an indirect configurations solution: http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx|||

...one year later

I run on a similar problem - I created xml configuration file where I put the destination server's connection string, planning to use another sql server configuration table where it would be convinient to change some package properties before running.

And as I understud, with the /Conf tag I could change the path of the xml conf file, but I could not, I dont know if I am wrong somewere but the /Conf tag for me just did not works.

Later I realized that the deploying utility changes the path in the package itself, so maybe that tag is not needed (better, less characters )

So I overcomed that question and resumed with that combination, it is just great for me like that.

I tryed with environment variable and with registry, but because of less privilegies on production I just could not make them work.

Conclusion: there are a lot of catches about SSIS, but it is interesting, it's foundations are good.

|||

Berzat,

Package configurations are working, and work well for most scenarios.

If you searchyou will find tons of working examples. Here is mine http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Notice that it uses an env. Variable; but you could use an XML file instead and get the same functionality.

|||

Thanks Rafael, indirect configuration is something else.

The /conf option for direct configuration does not working as it is pointed in the ssis documentation and nobody wants to confirm that or to prove the oposite...

|||

Bertzat,

You could prove it then; You just have to provide a 'repro'; a series of steps that everyone can follow and get the same results. Then post it in the connect site; the SQL server team will appreciate it.

|||

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

|||

Berzat wrote:

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

I believe Michael was using relative file paths to point to his configuration file. Absolute paths are a requirement inside SSIS. And then when deploying, you need to ensure that the file path exists and the config file resides there. So no, it's not enough. If you are having a different issue, then please post your steps necessary to recreate your issue.

|||Phil, I agree with your points, but I did want to mention that absolute paths are not required - though they are a good practice and head off a lot of troubleshooting issues. If you are extremely careful, you can use relative paths. However, I really don't recommend it as general practice.

Configuration Packages are still in Beta

I would just like to give my feedback on the configuration packages implementation in SSIS:

THIS STUFF SHOULD BE A NO BRAINER AS IT IS A COMMON REQUIREMENT ON ETL PROJECTS.

I have trolled through these forums and found that I am not alone on this issue. Note for SSIS Team: Please bring out a HOWTO on this subject that details all the little work arounds because intuitively used it does not work.

Consider this:

I develop in BIDS on my client and everything runs. I create a config package and I choose only the Name and ConnectionString Properties for each Connection Manager. (Thanks to Jamie for that tip)

I build the app and a deployment utility is created. I navigate to the deployment directory, and deploy. I point to a mapped share on the production server, change all the properties for production and continue until finished.

On the production server I run a script containing:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx >> D:\SSIS\Source\logs\Source.log

I get an error: Cannot find the configuration file: source.dtsConfig

I tried putting the following in the script:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx /CONFIGFILE D:\SSIS\Source\code\source.dtsconfig >> D:\SSIS\Source\logs\Source.log

Still: Cannot find the configuration file: source.dtsConfig

I have to MANUALLY edit the dtsx file to put the path in from the the name of the config file and THEN it works.

Thinking that it might not like a mapped share from desktop to deploy to, I copIED the contents of the depLoyMENT directory on my desktop to a folder on the production server and deploy from there. It should pick up the proper path from there. NOT!!!!

So it was back to editing the dtsx file.

So there you have it. I await all comments with great interest.

I hope this is all fixed in SP2, Because I don't want to waste anymore time on what should be a simple task.

Michael Morrissey.
ETL and BI Developer.

if i'm not mistaken, your issue cries out for an indirect configurations solution: http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx|||

...one year later

I run on a similar problem - I created xml configuration file where I put the destination server's connection string, planning to use another sql server configuration table where it would be convinient to change some package properties before running.

And as I understud, with the /Conf tag I could change the path of the xml conf file, but I could not, I dont know if I am wrong somewere but the /Conf tag for me just did not works.

Later I realized that the deploying utility changes the path in the package itself, so maybe that tag is not needed (better, less characters )

So I overcomed that question and resumed with that combination, it is just great for me like that.

I tryed with environment variable and with registry, but because of less privilegies on production I just could not make them work.

Conclusion: there are a lot of catches about SSIS, but it is interesting, it's foundations are good.

|||

Berzat,

Package configurations are working, and work well for most scenarios.

If you searchyou will find tons of working examples. Here is mine http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Notice that it uses an env. Variable; but you could use an XML file instead and get the same functionality.

|||

Thanks Rafael, indirect configuration is something else.

The /conf option for direct configuration does not working as it is pointed in the ssis documentation and nobody wants to confirm that or to prove the oposite...

|||

Bertzat,

You could prove it then; You just have to provide a 'repro'; a series of steps that everyone can follow and get the same results. Then post it in the connect site; the SQL server team will appreciate it.

|||

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

|||

Berzat wrote:

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

I believe Michael was using relative file paths to point to his configuration file. Absolute paths are a requirement inside SSIS. And then when deploying, you need to ensure that the file path exists and the config file resides there. So no, it's not enough. If you are having a different issue, then please post your steps necessary to recreate your issue.

|||Phil, I agree with your points, but I did want to mention that absolute paths are not required - though they are a good practice and head off a lot of troubleshooting issues. If you are extremely careful, you can use relative paths. However, I really don't recommend it as general practice.

Configuration Manager ?

Each time you start a SSIS project you have Solutions Configuration showing "Development". In the drop down box you can select "Configuration Manager..."

Can someone tell me the use of this, and is it related to the package configurations somehow. I've tried to create new Solution Configurations like "Test" and "Production" with the purpose of binding different configuration files and deplymentfolders to each Solution Configuration but still it seems like this isn't the way i should be used...

This is something inherent to Visual Studio rather than BIDS or SSIS. It doesn't have any relevance to package configurations that we know and love (!!!).

I don't know how this VS feature is supposed to be used so I say whether it would be useful for SSIS or not. I'd like to know though!!!

-Jamie

|||

The only thing i could see any use of this was to create 3 enviroments - Development, Test and Production.

I each of those i'll set the outputpath to bin\Development, bin\Test, bin\Production

So when i build the package i can choose the enviroment to build it to so that i can keep those files sepperate....

|||

Is there a way to reference the current value within a variable ?

This would be helpfull for having the servername depend on the selected configuration.

Thanks in advance,

Geert

|||

What you are asking and what cpgl described seems totally unnecessary if you only use the built in Configuration support for SSIS. Things like file paths are server names should be changed through configurations, not coded into the package during "build".

I would expect each environment to have the required configiration resources in place, so there should be nothing to change in the package to prepare it for an environment. If you do change the package between environments it just defeats the purpose of having a test environment as what you tested is not what you will then promote to production. It may be a minor change, but being strict, no changes should be allowed.

The only exception I can see is when environments are combined on machines, and for that I'd recomend passing in a parameter through the execution host, e.g. DTEXEC /SET

Configuration for Configuration Package? Is this Possible?

Hi Guys,

Here's the current scenario:

I am creating a windows application to manage my ssis packages.

My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.

However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.

The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.

Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.

Thanks,

kervy

There are a number of ways. I would store the connection string of the connection manager in an XML configuration file.

-Jamie

|||

it's not the connection string of the connection manager, but the connection to the package configuration table,

currently my package configurations are looking in the "SSIS Configurations" table.. but I want my application to let the dtsx switch to another configuration table named "SSIS Configurations2" both have the same schema but different values. is there a "configuration" property for the configuration table connection?

|||

Ah OK. Well I've just looked at the setup screen for SQL Server configs for the first time in my life and seen that you can store the config location in an environment variable. I don't know exactly what it does but have you explored that? BOL should contain details to help you.

-Jamie

Configuration File not always used

I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.

When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).

Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.

|||

I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...

What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

Configuration File not always used

I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.

When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).

Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.

|||

I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...

What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

sqlsql

Configuration file for Connection Info (how do i set password)

Hi All,

I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.

I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.

Thanks,
Dave

check this out, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516292&SiteID=1

kushpaw

|||Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.|||

tenaciousd wrote:

Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.

I recommend you only store the ConnectionString property. That is an amalgamation of all the other properties that you listed there.

-Jamie

sqlsql

Configuration Changes

We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.

Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.

I think this is possible, yeah. If your packages need to be executed in different scenarios then you effectively need to "parameterise" those packages. Is that correct?

If so, you should lookat using the /SET option of dtexec.exe. It is provided for just this situation.

Configurations aren't really what you after. They are more about making your packages location independent, whereas it seems to me that you want to parameterise your packages.

Good luck with it.

-Jamie

|||

Thanks. We'll have to think through this a bit. *If* we could change the filter at package invocation then configurations would fit our needs perfectly. We'll probabbly look for a way to set several SSIS variables from a SQL table and use a /SET to specify which set of values to populate variables from.

I think we could pull it off with configurations using multiple tables, but I'm not fond of that either. I guess it's rough to be picky. :)

|||That's the point of configuration tables in SQL Server though -- to make things flexible...

Perhaps you could store your multiple configurations in a table and then with a stored procedure or something, update the SSIS Configuration table with the appropriate values.|||

We actually do that for sub packages, but it doesn't seem appropriate here. If I update the single set of SSIS values from two different jobs at close to the same time, it's likely one of them will run with the wrong values. Tracking that down seems like more effort than I'd like to spend if it occured.

My colleague had an idea we're investigating. Using environment values. We're thinking that perhaps by creating a batch file that sets a value on the fly and then issues a dtexec that we might achieve the result we want without putting values permanently into the evironment or exposing sensitive data.

|||

Ok, I think we have it working. We created a batch file called SSISWithConfiguration.bat:
SET env="Configuration";"[dbo].[SSIS Configurations]";%2
dtexec /DTS %1

In the job we just pass the path to the SSIS package and the Configuration filter values we want to use. The environment variables will be separate for each invocation and we only have one batch file to deploy ever. (We always call a configuration connection called Configuration). Execution looks like:
C:\MSSQL\SSISWithConfiguration.bat "\MSDB\folder\packageName" "New filter name"

In the package we just create an indirect SQL Server configuration with the environment variable as env.

During development we can use configuration settings directly and then when ready to deploy a reusable package switch to indirect (that way we don't have to have permanent environment variables on the developers desktops and the configuration table in development will get updated).

Finally, in the package, we have an XML file configuration listed first to move the Configuration connection between development, test, and production. This gets deployed once on each developers box and once on each server.

The last piece we wrote was a small utility that will let us pick a configuration setting and will write the SQL script to insert the configuration settings after deleting any existing values. We save that three times and change the values for development, test, and production.

Thanks for all the ideas and support. Sometimes it's just looking at stuff a bit different :)

config file question

In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.

I just want to be sure I don't need both for my package to run successfully.

You can add only 1 or as many configuration values as you want in a single file. As a good practice you should have both values set via configurations; just to cover a possible change in the flat file location. You could also create a separate config file for each property.

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:

Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:

Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:

Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:

Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!

Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

Conditional Split Component - annotation issues

Greetings SSIS friends,

When I configured my conditional split component (directing the data flow in 2 directions) The annotation does not align properly with the lines. Is there anyway to shift the text as to make more presentable?

Thanks for your help in advance.

No, you can't.

You can turn off the annotation, and then add your own, though, by right-clicking on the background and selecting "Add Annotation."|||

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

Double click on the flow line and then set the PathAnnotation (found in the General section under Design) to "Never."|||

dreameR.78 wrote:

Hi Phil,

Excuse the silly questions! So how do I turn off the annotations?! I think I'd much rather add my own as the automatic ones don't seem to align themselves with the data flow lines. Shame.

If you think there's an issue here then please raise it at Connect (http://connect.microsoft.com/sqlserver/feedback)

-Jamie

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

Concatinating Select Results

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrders
SET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

There are probably a number of ways of doing this. The first thing that occurs to me is to use an asynchronous script component that takes a set of data (ordered by ReqNum). Inside the script component loop over the set of data, concatenating comments for each ReqNum.

-Jamie

|||Hey Jamie,

Thanks for the response. I'm very new to SSIS so that's a little over my head. Could you elaborate a little more on all that? Or can you think of an easier way of accomplishing this?

Thanks

Lee.