Pad Spaces in FFConnection properties?

This is a considerably LOW priority, but is there a way to padd spaces in ssis flat file connection properties? We have cases where a CSV file will read in and throw a truncation error because the field length is greater then defined. However, the CSV file might look like ,"ABC ", where a number of trailing spaces are found. Is there some way to handle this without having to adjust teh field length, maybe ignore whitespace at the source?There is no way at the Flat File Source to ignore trailing whitespace, however there is a work around. Say in this case your field length is 3, and if there are trailing spaces after that, you want to ignore them. You can then use "Ignore Failure" for Truncation Error Disposition. That is, in the Error Output tab for the Flat File Source, for Truncation of this column, choose "Ignore Failure", instead of "Fail Component". By doing this, it will only get the specified characters, and ignore the rest, whether they are trailing white spaces, or some other data there. You have to make sure that this is not causing you to accept some data which you otherwise wanted to fail on really.|||

That is partially the problem, we only want to ignore whitespaces, but if there is actual chars then we would want to throw truncation error.

Thanks for your thoughts...

Package Stuck on "Pre-Execute phase is beginning."

Hi all,

I have a monster SQL query (SQL Server connection) that I'm trying to export to flat file. My package appears to just stick on the "Pre-Execute phase is beginning" stage. (I left it running overnight, and it's still going... no error message, I think it's just hung. If I include a "Top 10" in the my source query it completes without a problem. I'm wondering if it's an out-of-memory issue, but when I've run into this in the past there have been error messages generated. I've tried using both a Data Reader and an OLEDB Datasource. Does anyone have any ideas of what's going on and how I might fix it?

Thanks!!!

JessYou should run the query in management studio and look at its explain plan... Chances are that it has nothing to do with SSIS. Perhaps you have locks in place that are blocking parts of this query such that it never completes?

You could always wrap the query into a view and use the view as the source to your package. That might help the pre-execute phase.

Also, try setting your DelayValidation property to TRUE for the connection manager that's being used for this query.|||

Thanks for your response. I finally got it working. Our off-shore team actually wrote the query for this project and it was fairly convoluted. I just have the pleasure of actually trying to RUN it.

Anyhow, there was no descernable blocking from sp_who2 'active.' While switching the source query over to a view (per your suggestion), I discovered that the source sql inserted a number of records into a (non-indexed) table variable via a cursor. Placing this data into a permanent, indexed table seems to be the change that got the package working.

I've done some more testing, and whenever I use a table variable in the source query I seem to get stuck at "Pre-Execute." Is this by-design?

Thanks again for your input.

Jess

|||

Hi Jessica,

It is never by design to get stuck.

Pre-execute is the time when your queries get prepared and all the problems in them are likely to show up at that point. Looking at the SQL Profiler might help you figure out what exactly is causing issues while queries are processed.

HTH.

Package still referencing old parameters from an old connection

I deleted and created a new OLE DB connection string then set all my connections to that string in my components in my SSIS package however below where it talks about EBN_TEMP1, that's an old database table that no longer exists after I unistalled and reinstalled SQL Server on this box. Why is it still refrencing old stuff? Is there some sort of refresh I have to do on my entire package due to the fact that I

1) Reinstalled SQL Server 2005

2) Deleted an old OLE DB Conenction my package was using (based on an old database that was in the previous SQL Server install) and createad a new OLE DB Connection in my package to point to a new database name after my reinstall of SQL Server 2005

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 1" and "Screen" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 2" and "CaseNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 3" and "BKYChapter" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 4" and "FileDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 6" and "DispositionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 23" and "BKUDA1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 24" and "RMSADDR2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 25" and "RMSCMPNAME_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 26" and "RMSADDR_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 27" and "RMSCITY_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 28" and "RMSSTATECD_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 29" and "RMSZIPCODE_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 30" and "RMSWORKPHN" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 31" and "BKYMEETDTE" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 34" and "RMSCMPNAME_2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "EBN_TEMP1" (528)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I don't remember the format of error message, but this string (EBN_TEMP1) is probably the name of data flow component (source, destination, etc), not the connection name. Just look around the package to find an object with such name. Or double click the error message to get editor for this component.sql

Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Package Fail connections on moving to server

I am working with the ssis packages.I am working in the development enviroment.Once i place them on the server all my connection to text files fails.I dont want to sit and Keep changing with respect to server again and again.

What is the best way to set connection like for example:in my development it can be on e:\data folder

But on server F:\Data

With me changing again and again, can this be set within the package in the development environment.

Please le me know.

Use a package configuration to populate a variable with the location of the files. Then use an expression on the connection manager to combine the file location variable with the hard-coded filenames.
|||

Would you please explain in little more details

Thanks in advance

|||If you aren't familiar with Package Configurations, you should read the topic in Books Online. You can use them to set the connection string on your flat file connection managers, as Jay suggests.

Package Configurations

Hi,

I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.

Thanks

With the above in mind, my end achievement would be to have the same packages deployed to my test and live environment at the same time but when I execute the packages in the test environment, it will use the connection string to the test environment and likewise when I execute the packages in the live environment, it must use the connection string to the live environment.

So, effectively, I am going to have a configuration file containing the connection string on both the live and test server. The packages therefore need to point to the respective files. Has anyone does something similar?

|||

Not sure is that is the reason of your issue; but BIDS will pickup changes to the environment variable only after close it and open it back.

The approach that you described in your second post is pretty common when you have 2 environments in separete machines. I don't see an issue on that.

Rafael Salas

|||Okay, sorted that out. Another issue that I have identified however is that the connection string that is now used seems to depend on who you have logged in to SSIS as (serious problem). For example, lets say on my test server, the configuration file is specifying 'Test' as the server name to use in the connection string. When I go to the physical server and get onto SSIS and check what the connection string is, it is correctly using 'Test' as the server name. Now, if I have to connect to SSIS on the test server from my machine and check what server name is been used in the connection string, it is showing the original server name that the package was deployed with (this is different to the server name specified in the config file on the test server). Do you know if this is a bug in SQL 2005 or am I missing something here?|||

Not sure if i am understanding this correctly. Anyway, when you use package configurations for setting up values at run time; they exist just at that time (at run time); whatever value are stored in the package the last time it was saved will be always there.

Rafael Salas

Package Configuration with Environment Variable

Hi,

I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.

Here goes..

SSIS package1:

Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.

Environment variable:

Pckg_config = <location of config file which has testcon1 and testcon2>

I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2

Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig

Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.

SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.

Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.

Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.

Thanx,

TusharYou should have only two connections (devcon1 and 2) defined in your package. Set up a single configuration file that sets the two connections. When you deploy to test, you need to create a new copy of the configuration, and alter the connection strings in it to point to your test environment. So you should be using the same package file, but different configuration files in each environment.|||Thanx for responding.

I have followed exactly what you said with my prior set up. I have a devEnv.dtsconfig with devcons and testEnv.dtsConfig with testcons.

The issue is with the package. While creating the package i created it with devcons. Additionally i have testcons which do nothing but sit with the package and get deployed on the server.

If i do not add testcons.....testEnv.dtsconfig gives connection errors....when trying to locate testcons.

Let me know if you need more info...

Thanx again.

Tushar
|||You should have only two connection managers in your package. You should not have testcon1 and testcon2. To create your 2nd configuration file, copy the first configuration file into a new folder - but keep the same name. Open it in notepad, and edit the <ConfiguredValue> tag to set it to your test connection.

When you run the package for test, use the DTEXEC /CONF option to specify the 2nd configuration file.

Package Configuration in SQL

I have an OLEDB SQL Connection that has a connection string that is set as and Expression using a User Variable (@.[User::server_name]). I pass the server name as a command line option. The Package Configuration table is being accessed using the setting for User::server_name in the package and not using the parameter entry for it.
The command being issued is similar to:

dtexec /FILE "packagename.dtsx" /SET "\package.variables[server_name]";dbserver

I am wondering if this by design? I would think that all parameter variables would be set prior to any other configuration entries are processed.

Thanks,
MikeHi Mike,

Do you get any warnings or errors with the command line you specified? Is there more than one variable called server_name with a different namespace or at different scope? You can also add the /rep v option and see if there is any other information that might be useful to determine what is happening.

Thanks,
Matt|||Here is the error I was getting:

Started: 11:27:03 AM
Warning: 2005-12-13 11:27:06.54
Code: 0x8002F304
Source: packagename
Description: A problem occurred with the following messages: "Invalid object
name 'dbo.SSISConfigurations'.".
End Warning
Warning: 2005-12-13 11:27:06.54
Code: 0x80012059
Source: packagename
Description: Failed to load at least one of the configuration entries for the
package. Check configurations entries and previous warnings to see descriptions
of which configuration failed.
End Warning

The table dbo.SSISConfigurations does not exist on the server I had set in the package, but does on the server I am passing as a parameter. This was done specifically to test for this type of issue, where when deployed to a client that does not have the server configured in the package.

Thanks,
Mike|||It looks to me that you try to apply configuration from a server that you want to be specified at runtime.

The problem is that configuration happens at load time and before any set from the command line.

The way I see it is to have define din the package a configuration that would set the desired server name in your variable and this configuration is applied before any other configuration.

HTH,
Ovidiu Burlacu|||Thanks for the feedback. You confirmed what I suspected about the order of evaluation between Configuration and Parameters.

I have attempted to run dtexec with /ConfigFile parameter but it errors with unable to locate the configuration file. I put the full path of the configuration file and it still errors:

dtexec /FILE "C:\SSIS\Package.dtsx" /CONFIGFILE "C:\SSIS\TestConfig.dtsConfig" /MAXCONCURRENT 1 /CHECKPOINTING OFF

I am running on IA64 system, so I tried both 32bit and 64bit dtexec programs and they error the same. So not sure where to go from here.

Thanks,
Mike|||The solution you are trying will not work and the reason is the sequence of configurations:
1. Configurations defined in the package
2. Configurations specified in the command line
You need to open the package in the designer and specify the configuration for your variable in the package and make sure that this is the first configuration in the list.

HTH,
Ovidiu|||

I have tried in the designer to set the configuration file, but it has a fully qualified path to the configuration file. So when deployed, it will not match correctly on a that system.

I would think that the system should process all command line parameters prior to loading any configurations. Maybe I should submit this as a wish list item. I just don't see how to override on a deployed system. This seems a lot like the Chicken and the Egg...

Thanks,
Mike

|||Since you can have multiple configurations in a you should have one configuration (the first one) that loads the server name from something that can be setup on each computer that will run packages (this could be an environment variable or a configuration file that will always be in a fixed location). Then the second configuration would be able to work because the first one would be able to already configure the server name.

What you want is problematic because it implies interaction in loading configurations from the utility that loads the package. If this was done then anyone could easily circumvent configuration loading by not requesting the package to load up its configurations. While you could argue the same thing is happening now just by the package not being able to find the configuration it is actually different because in the first case there would be no warning informing you that the configuration was not applies, whereas in the second there is always a warning and therefore you are aware of the problem as opposed to the package just not running and you having no idea why. All this being said please feel free to submit this as a wish list item, I was just trying to give you insight as to why it is the way it is.

Thanks,
Matt

Package configuration help !

hi,

I have a package that executes nicely.
When I enable "Package Configuration", create an XML config file and only select the Connection Managers (2 file connection managers and 1 data source), I get the following error (when executing the package in debug mode from designer):
Information: 0x40016041 at MyPackage: The package is attempting to configure from the XML file "C:\src\myconfig.dtsConfig".
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task - Extract my_file_src, DTS.Pipeline: Validation phase is beginning
Error: 0xC0202009 at MyPackage, Connection manager "MyConnMgr": An OLE DB error has occurred. Error code: 0x80040E4D
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'."
Error: 0xC020801C at Data Flow Task - Extract my_file_src, Lookup - column1 from table1 [6012]: The AcquireConnection method call to the connection manager "MyConnMgr" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task - Extract my_file_src, DTS.Pipeline: component "Lookup - column1 from table1" (6012) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task - Extract my_file_src, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task - Extract my_file_src: There were errors during task validation.
SSIS package "MyPackage.dtsx" finished: Failure.
The program '[2136] MyPackage.dtsx: DTS' has exited with code 0 (0x0).
I guess I am making some basic mistake.
Somebody please throw some light.

Thanks in advance.
NiteshYou might open up the package configuration file (C:\src\myconfig.dtsConfig"), and see if it has an entry for username "sa" in it -- the error you posted suggests that it has an incorrect password for that account.
|||Also, Note that the configuration message is simply an informational message that let's you know that the package is getting configured. It's not an error or warning.
Look at the open package and verify that the password is correct for the connections.
K

package configuration for generic pathnames and specific filenames

It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages

Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.

For example I use the following flat files in my project:

c:\AAA.txt

c:\BBB.txt

c:\CCC.txt

If I move the packages to the production database the flat file sources are located in another directory.

So in fact for the flat file sources there is a generic part for all files (in this case 'c:\ ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').

Can I indicate this in the package configurations sections? And how?

Thanks,

John

Create two variables in your package. One to hold the "generic" part of the path, the other to hold the specific part of the path. Include these two variables in your configuration file so that you can alter them.

Then using expressions on the flat file source, you can concatenate the two variables to form the full path, which is dynamically adjusted based on the configuration file.|||

Using expressions in combination with the variables solved my problem.

Thanks a lot Phil

package configuration are ignored

hi everyone

I am putting my connection string in package configuration file, which i create through package configuration option.

But connection is not picking up value where as other varibles are picking up values from same file.

Is there is any other setting that we have to set for connection string.

Thanks and regards

Rahul Kumar

Chek the execution log of the package and see if there is any warning about configurations not taking place. It sounds weird that other variables are picking up the configuration values right.|||

hi

I got it right now.

Actually i was not giving connection string in design time and wanted package to pick it from config file.So package was not running.

Mistake i was making was i wasn't setting delay validation as true.

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Package Configuration

I have created a package configuration to read the connectionstring for the oledb connection manager.

Saved the file as Environment.dtsConfig in the same directory as the other packages.

Do the packages refer to the configuration file automatically?

Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?

Thanks

Yes and no.

You can check what Configurations are "bound" to a package, open the package and then go to the Configurations menu option in the designer. The first dialog shows you all configurations that will be applied to that package.

There is no difference in the way you use DTEXEC now that the package has a configuration.

Package Configuration

Here is the scenario we are

trying to use; (it a bit long winded please bare with me)

There are 2 Packages Parent

and Child. Both have 2 Connection Managers (��Configuration�� & ��Data Connection��).

The ��Data Connection�� has an invalid connection set to start with this is so

that we can deploy this to multiple places and use a SQL table to hold the

configuration.

At runtime ��Configuration�� is

set to a valid SQL2005 database connection via the command line or in BI but ��Data

Connection�� is left invalid.

The Package Configuration point

to an SQL server using ��Configuration�� this has the connection string for the ��Data

Connection�� Connection Manager (a valid connection string). The package errors

with can not acquire connection.

The first task is to put the

Connection string of the Connection manager ��Configuration�� in to a variable.

Then the child package is called.

The Child package has the same

2 connections ��Configuration�� & ��Data Connection�� both are Invalid at this

time.

1) We use the Package

Configuration to populate a variable from the parent package which holds the ��Configuration��

connection string.

2) We use a Package

Configuration to populate then Configuration�� connection from the parent

package which holds the ��Configuration�� connection.

3) We use a Package

Configuration to populate the ��Data Connection�� Connection Manager using the ��Configuration��

connection. Which should be Set via step 2 here.

The First task on the child

package has a bit of script to check all the connection manager return a valid

connection to make sure database are up and running before connection. This

fails at the moment return in an error.

We want to use the same code

in multiple environments and as such don��t want to make code changes that are really

only environment changes.

IE connection string to

servers. I can��t seem to see anything saying we are doping anything wrong but if

the connections are all set to a valid connection before running this seems to

work. When invalid connection are used this seems to fall over.

Peter you might be seeing the result of a known issue. When you define a package configuration using 'Parent package variable' type; no matter in which order you place it; it will be executed last.

You can see the details here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

PeterCh wrote:

The Child package has the same 2 connections ��Configuration�� & ��Data Connection�� both are Invalid at this time.

1) We use the Package Configuration to populate a variable from the parent package which holds the ��Configuration�� connection string.

2) We use a Package Configuration to populate then Configuration�� connection from the parent package which holds the ��Configuration�� connection.

3) We use a Package Configuration to populate the ��Data Connection�� Connection Manager using the ��Configuration�� connection. Which should be Set via step 2 here.

With this bug, your 2^nd step occurs last; so your 3^rd step would try to use the invalid connection string defined in the package; in consequence the configuration will not be actually performed.

Just to check, when running the packages in debug mode in BIDS, go to the progress tab and see in which order the package configurations are performed.

As a work around and since this issue only affects the behavior of ��parent package variable�� configuration type; instead of sending the connection string of ��Configuration�� via command line when calling the parent package; you can try to place it in a environment variable or file and then create a configuration on every package. Then create additional package configurations for the remaining connections managers using ��configuration�� connection manager.

I hope this help

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1347857&SiteID=1

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1347857&SiteID=1

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.

Package config errors - VS_NEEDSNEWMETADATA

Hi everyone,
I am having issues with using package configurations when just chaging the ServerName and InitialCatalog vars of a Connection Manager. I have DelayValidation = True on the Data Flow Task that is erroring out, but I am still recieving a VS_NEEDSNEWMETADATA error before the package is executed. The 2 boxes have identical tables I'm trying to access, although 1 is 2005 and the other is 2000. Any thoughts? I thought that package configs were supposed to be quick and painless?
Thanks,
Adrian
NeedsNewMetadata means that the component is detecting a difference between the table you initialized it with and the one you are running against, which is not surprising if you are changing SQL Server versions. Configurations are supposed to be quick and painless if used in a quick and painless way. For example you wouldn't expect them to work if you switched between SQL Server and DB/2 for the database (at least I hope not . Well 2000 and 2005 are quite different and this difference is manifesting itself by the component determining it needs a metadata refresh.

HTH,
Matt|||I would have that impression if using a non-Microsoft db product. However since the datatypes were the same and the connection manager type(oledb) worked to connect to both I assumed there would be no problems. But we know what they say about someone who assumes huh? If that is to be expected then I suppose there is nothing that can be done.
Thanks,
Adrian
|||Can you verify that the column names are identical as well as their types (and sizes)? In general we don't cause this type of failure based on the database back end but the component is determining that there is some difference between the metadata if has for the table and the metadata that the table being attached to is presenting to is. All I meant was that when switching to different databases it is easier for the mismatch to occur. I am not certain that was clear when rereading my post.

Thanks,
Matt|||I did verify the datatypes and sizes. I accepted 2005's table defaults in all other areas, so maybe something has changed there that I need to check. I will post back if those are identical and there are still problems.
Thanks,
Adrian
|||The column names are also very important as that is how we determine a metadata match. So if the names change then you will 100% of the time get this problem even if the types and sizes are identical.

Thanks,
Matt|||Are the column name matches case-sensitive? If so, there's my problem.
|||Yes, the check is case-sensitive. This is so we don't miss catching a bad case when a database is case-sensitive for column names.

Thanks,
Matt|||

Although I have seen this thread at a very later date, It helped me a lot . I was facing issues with VS_NEEDSNEWMETADATA error and the reason was because of case sensitive column names.

Thanks a lot

|||

We worked around this by setting the source to a SQL command rather than a Table/View. Do that in the Source Editor "Data Access Mode" box. Then type in your query... Select Column1 from Table1. No matter the case of Column1 on the back end, it woudl succeeds in our case.

This workaround keeps validation from being so strict on the column names.

Since our back end was case-insensitive SQL, our back end can handle the mixed cases of columns, so your SQL Command can succeed against the data source irrespective of column case, and so SSIS is not aware that the back end column metadata doesn't match the case of the SSIS package column labels.