Derived Column Task failing with error 0xC0049067

I have a package that fails as soon as it hits the first Data Flow that contains a Derived Column task. The task takes three date columns and looks for a date of 6/6/2079. If it is there, it is replaced with a NULL. This task worked fine until I installed the Non-CTP version of SQL 2005 SP1, earlier today. (I went from RTM 9.0.1399 to SP1 9.0.2047)Does anyone have any ideas?

Here is the error I am trapping:

An error occurred while evaluating the function.

The "component "Update Max Date Value to NULL" (346)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "WeekEndingDate" (455)" specifies failure on error. An error occurred on the specified object of the specified component.

The ProcessInput method on component "Update Max Date Value to NULL" (346) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Thread "WorkThread0" has exited with error code 0xC0209029.

I tried to configure the error output to ignore the failure. The task succeeded. It did not insert anything, however, for the three columns in question. It then failed on the next Data Flow that contained a Derived Column task. I tried to pre-populate the columns with a default date, but no go. Any help would be appreciated.

Nothing wrong with the concept and I can do a similar thing in SP1. What is your expression and column types?|||

The date columns come out of an Access database. I convert them to a string in a Data Conversion task and then validate that they are valid dates in a Script Component task. If they are invalid, I assign them a date of 6/6/2079. When they exit the Script task they are converted back to a date. The Derived Column task is next and it looks for the year 2079 to convert it to a NULL. Here is an example of one of the expressions:

YEAR([Check for Valid Dates].LastWorkedDate) == 2079 ? NULL(DT_DBTIMESTAMP) : [Check for Valid Dates].LastWorkedDate

This is where it fails. One thing I failed to mention before is that if I execute the task by itself it works properly. However, if I run the entire package (100+ tasks) it fails as soon as it hits the first task that contains a Derived Column task.

|||Turns out it is not the first Data Flow with a Derived Column task that is failing. I have one about 8 steps prior that runs just fine. I am wondering if it has something to do with the expression language. I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how. Any suggestions from anyone would be greatly appreciated. I have been stuck on this for a couple of days and I really need to move on. I am dreading the thought of uninstalling\reinstalling SQL2000 and 2005 on my machine but will if I have to. Please help!!!!|||I uninstalled and reinstalled SQL 2005 and then attempted to run my package and it worked. There must be something that changed about the way it evaluates expressions in Derived Column tasks or maybe it is the expression language itself. Either way I am going to have to move on without SP1 until residual issues like these are dealt with. I am going to submit a bug to Microsoft about it. If anyone comes up with anything, please post a response.|||

I have been unable to reproduce this either on SQL Server 2005 RTM or SP1.

Could you perhaps hook up the error output of the derived column and redirect error rows, and then see what value(s) of LastWorkedDate it's failing on?

Thanks,
mark

|||I apologize for taking so long to get back to you. Unfortunately, I have already reinstalled SQL to RTM -SP1. I lost two days trying to troubleshoot the problem and had to give up for now. As soon as I have a chance I will reinstall SP1 and try again. For now however I have to live without it. Thank you very much for trying to reproduce the error. I will post more about this once I have the time.|||

DatabaseOgre wrote:

I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how.

I suspect that what you suggested is indeed the problem. YEAR will fail if the date is outside of the SQL range.

If you have a chance to try redirecting rows to see if the values are indeed outside of that range, that would help us be sure that we have figured this one out. In the meantime, I will update the bug with this information.

|||

Sorry. I went back to the post and reade it a little slower this time. The post refers specifically to the DATEPART function with the YEAR argument. Here is a quote:

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jamie Thomson verified it for him and it was submitted as a bug for not being able to handle dates outside of the T-SQL date range. I checked my data to look for any dates that were outside of the range (pre-package) and did not find any. I also tried handling any such dates inside of a Script Task (as a just in case) which ran prior to the Derived Column Task and still received the error. When I get some time I will take a look at my source data again and see if there is anything there. I doubt it however because I am using the same package and same data source with SQL2005 RTM and I am not getting any errors. I probably wouldn't change much about the bug I submitted just yet.

|||

The value being out of range was my best guess for the cause of the error. If you know the values are all in range, then something else is wrong.

The best thing now, I think, would be for you to try redirecting the error rows and see what values are causing the problem. Once we know that, I will be able to try to reproduce the problem.

Thanks!
Mark

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

sql

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

Derived column expression question......

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

mjanzou wrote:

Greetings,

I have an existing 2000 DTS package that uses the following case statement:

Case

When TERMS_PERCENT ='0'

then 0

else cast(TERMS_PERCENT as decimal(6,2))/100

end as TermsPct

to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

Something like this perhaps?

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

|||

Jamie Thomson wrote:

[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0

-Jamie

The TRUE and FALSE values are reversed . Try:

Code Snippet

[TERMS_PERCENT]== "0" ? (DT_NUMERIC,6,2)0 : ((DT_NUMERIC,6,2)[TERMS_PERCENT]) / 100

|||

Ahhh goddamnit!!!

Deployment Utility or Import Package?

In reference to the question raised in this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460591&SiteID=1

Since I'm not able to create a deployment utility, when a config file is shared among multiple packages and also I cannot get the permission from Sys Admins to use Env. Variables I'm struck.

Now I'm thinking of importing the package to Sql Server from the file system. Is there any caveats in this approach? especially regarding the config files?

[edit]
Also, do I need any special permissions to view the Integeration Services node in Management Studio? We are using Integerated Authentication, neither do I'm able to run sp_start_job sp in the msdb database.
[/edit]

Thanks

You might want to look at DTUTIL for deployment. It's a little more work than just building the project in SSIS, but it is more flexible.

With the config files, you just need to be sure they are accessible from the package once it is in SQL Server.

Deployment problems - no components for DataFlow

I’ve created a SSIS package that has 2 tasks. The first is selecting some information from a SQL Server table and the second is exporting this information to a flat file.

I created the package on my desktop client and I am able to run it successfully in Debug mode from my client. The information is selected from the LIVE SQL Server database and the file is created on the LIVE SQL Server.

I have built the package for deployment, copied it to the LIVE SQL Server and have run the manifest file to deploy it.

I’ve logged into the SSIS on the LIVE server and the package is listed there. However when I right click on it and run it I get some error messages indicating that the dataflow tasks have no components, presumably the select statement and the export to the flat file.

It’s as if when I build the package the data components are not being included or when I deploy it on the LIVE server the data components are not being deployed.

Does anyone have suggestions?

Could you report the actual error message you are getting? Empty data flows only generate warnings, not errors.|||

The exact error is:

DocumentL Listing

Validation has started.

Export Document Listing

Validation has started

Warning: The DataFlow task has no components. Add components or remove the task

Validation is completed

Validation has started

Warning: The DataFlow task has no components. Add components or remove the task

...

|||

Rob,

I know about this problem only too well. Are you using a source control system?

-Jamie

|||

We are using Visual source Safe, but I do not believe that it is installed on any of the computers discussed here.

Are you saying that this may be a problem with the Source Safe integration with SQL 2005?

|||

Rob Woods wrote:

We are using Visual source Safe, but I do not believe that it is installed on any of the computers discussed here.

Rob Woods wrote:

So that package that we're talking about. Does it ever get stored in SourceSafe?

Are you saying that this may be a problem with the Source Safe integration with SQL 2005?

Not exactly. There's a problem with all source control systems when used in conjunction with the SSIS Designer - I have seen it happen with SourceSafe and TFS. The SSIS team are aware of it and are investigating. Long answer is that the contents of the data-flow doesn't get serialized correctly into the .dtsx file due to some sort of problem when used with a source control system. Short answer is that there is a bug.

I really should blog this

-Jamie

|||So is there a work around?|||

Rob Woods wrote:

So is there a work around?

Best I can offer is that seeing as you are using a source control system you can go back and find the most recent version that still contains all the components, and redo all of your changes. Not great!

-Jamie

|||

Jamie Thomson wrote:

I really should blog this

And now I have done: http://blogs.conchango.com/jamiethomson/archive/2007/04/24/SSIS_3A00_-Bug-when-using-source-control-systems.aspx

-Jamie

deployment options

Hello all,

I am considering the different options for package deployment on the server.

Until now, I have found several different ways to deploy packages to the server (File System):

1. Using the Import option from the Management Studio (only one by one)

2. Using the Deployment Utility (Needs building the whole project. Opens all the packages in debugging mode, cannot deploy to different folders)

3. Using the dtutil by constructing a command line for each package deployment. (complicated)

4. Simply copying the files from the local project folder to the "Program Files\Microsoft SQL Server\90\DTS\Packages" folder on the server.

Does anyone have any other suggestions for deployment?

The 4^th seems to be the easiest one, but I seen anybody suggesting such an action. What's the downside of such an action?

Thanks,

Liran

The 4th option IS easiest but you do have the potential for human error. You could easily write a batch file to do the same thing however and just amend that as necassary.

-Jamie

Deployment issue : COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not va

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server, I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

In my Bin folder, I do have the reference to the Interop.DTS.dll. The web server admin tells me that the dtsffile.dll, dtspkg.dll, dtspump.dll and axscphst.dll are registered onto the web server.

I am struggling to find the reasons for this. I have tried to replicate this error onto my machine to help me finding out the cause of this problem but in vain!

Can you help please?

Thank you.

Hello,

Just an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job.

Hope this helps.

Deployment issue : COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either no

Hello,

I have a asp.net application that calls a DTS package. This application is running fine on my machine (where SQL Server and Web Server are running). When I moved the application to a Web Server, I am getting the following error due to the DTS package.

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

In my Bin folder, I do have the reference to the Interop.DTS.dll. The web server admin tells me that the dtsffile.dll, dtspkg.dll, dtspump.dll and axscphst.dll are registered onto the web server.

I am struggling to find the reasons for this. I have tried to replicate this error onto my machine to help me finding out the cause of this problem but in vain!

Can you help please?

Thank you.

Hello,

Just an update on this issue.
Basically, I have never been able to manage to sort this one out, despite viewing many articles on the net.

However, I just want to share one option to overcome this problem.

You can invoke your DTS package through a JOB using SP_START_JOB stored proc. This SP Instructs SQL Server Agent to execute a job immediately. This would do the job of executing the package!!! Of course, you will need the right to run the job.

Hope this helps.

Deployment and Configuration

Hi:

I have a SSIS package on my local machine, and would like to deploy it to DEV server. Which files should i be moving to the SQL DEV Server? and where? How do I modify connection managers or is there a way to do that from the Management Studio or some other way? Thanks and I would definitely appreciate some prompt advise.

Someone will provide more information, I'm sure, but have you searched this forum for "deploy"?|||

Yes, I have checked out some posts on Deployment, but havent ran into anything standard. There seems to be more than one way of deploying. I would like to follow best practices. Thanks.

|||That's the beauty of SSIS. There are many ways to accomplish one task. The "best practice" is usually best determined on your own so that it fits your way of life. We each have our own requirements/standards so what my best practices are may not be Jamie's, or Rafael's, or.....|||

I prefer to have my packages as File system files (.dtsx); then all I have to do is to move (copy the files) them into a folder in the server. I use SQL Server table based package configurations to change connection strings at run time (and any other required property). I know there is a Deployment wizard in BIDS but I have never looked into that; so you do the research. Some times I use the SET option in the execution command line to override properties in the packages while testing.

As Phil points out; the best practice sometimes depends in your preferences, IT standards, etc....

|||

Lets say I would like to use the "copy over the files" method. What files am I moving over? and where on the server? Also for changing connection strings, how do i set the properties? Where? Thanks.

|||

Only the .dtsx files are required. In case you are using package configurations based on a XML file; you need to copy and secure it as appropriate. You choose the location; just keep in mind that that location needs to be accessible to the user running the packages.

To set connection strings; you can use package configurations; I use a combination of XML file to set the connection string of my SQL server based configuration table. If you search for 'Package configurations' you will find a lot of info. Jamie Thomson's blog, http://www.sqlis.com/ and BOL have some good info.

|||

The package I have will be scheduled daily, although, the connection strings config is only meant for Deployment Process, because the DTSX has to go thru DEV, QA, BETA, and onwards to PROD. Thanks.

|||

MA2005 wrote:

The package I have will be scheduled daily, although, the connection strings config is only meant for Deployment Process, because the DTSX has to go thru DEV, QA, BETA, and onwards to PROD. Thanks.

Your scenario is a very common one. SQL Server agent will be your best bet for scheduling your packages. I too, like Rafael, prefer to store my packages in the file system, versus storing them in MSDB.|||

If the only thing that needs to be changed/configured is a single connection string, and you have only one package (no parent package calling child packages)...you may want to use the SET option of the package execution command line instead of package configurations. I don’t like that approach when I have many packages that need to be configured in the same way; as I have to maintain every command line, as opposed of maintaining an entry in a config file/table

Deployment

Hi all,
Can someone please tell me the (.ocx) or other dependencies to include in my deployment package for crystal reports 10. I am using the "crystal active X report viewer library 10.0" in VB 6.0.
Thanks
LarryFound a useful resource on Crystal Deployment.

http://support.businessobjects.com/communityCS/TechnicalPapers/rdc10_deployment.pdf

Larry

Deploying SSIS Package

This maybe simple question but I can not seem to figure it out. If you use the deployment utility to deploy your package how do you specify a different SSIS directory to install it to. For instance when I'm in SSIS under stored packages > MSDB > I created a folder called package 1. I want to install to that folder. The only way I can do it is to manually import the package from SSIS.Try File->"Save Copy of xxxx as" when in a package.

That's another option for ya.|||

I guess it is a known limitation:

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

Deploying SQL Server packaged application

I developed an application using C# and SQL Server 2005. I want to
know how to package such an application so that users can download it
from my site, install it and start using it. Previously I have done
this with my VB/MS Access applications. There was no further
requirements as only blank MDB file need to be included. But I don't
know how to package an app. with SQL Server. How to configure the
setup so that on installing SQL Server along with the blank data files
are automatically installed on user's computer.It depends on variuos factors.
1. SQL Server 2005.
If your app uses SQL Server 2005 Sd or up, you probably do not distribute
SQL Server2005 itself. However, if you use SQL Server Express, you may wan
to include both SQL Server Express and the database. If you do want to
include SQL Server Express, your app being server app (such as web app) or
desktop app may make different. For desktop app, do you want to install SQL
Server Express on all user computers (so the data is not shared), or you
only need one SQL Server on the LAN and all users of youe desktop app share
the data (thus, only install one instance of SQL Server somewhere on the
LAN). You can search the net on the topic on packaging SQL Server Express
into installation package. It may not as complicated as imaging, but I never
did it and personally, I'd rather seperate the installation of SQL Server
Express from my application.
2. Database itsellf.
You can use script to create all SQL Server objects (database, views, SPs,
user logins...) during installation;
But simply including clean database file (*.mdf/*.ldf) into the package and
attach it to a ready SQL Server durinig installation would be easier.
However, after attaching database file, you may still need to run some
scripts to perform some user specific tasks, such as set/change SQL Server
login, database user...
"RP" <rpk.general@.gmail.com> wrote in message
news:1192532194.579961.226780@.e34g2000pro.googlegroups.com...
>I developed an application using C# and SQL Server 2005. I want to
> know how to package such an application so that users can download it
> from my site, install it and start using it. Previously I have done
> this with my VB/MS Access applications. There was no further
> requirements as only blank MDB file need to be included. But I don't
> know how to package an app. with SQL Server. How to configure the
> setup so that on installing SQL Server along with the blank data files
> are automatically installed on user's computer.
>

Deploying packages with SQL Server based configuration

Hello,

We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797

)

I searched for a viable answer, work-around, or hotfix that would address this issue but found none.

The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.

The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.

Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.

Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?

Thanks in advance!

In terms of environment, 64 bit SSIS (w/ post SP1 hotfix) and Windows 2003 x64 SP1, we have the same setup. I am also running this exact scenario and have no problem repointing the SQL server Configuration's connection manager's connection string via a previous direct XML configuration.

So, the only thing I can tell you is to look for configuration warnings (unable to read configuration), which is a dead giveaway that you'll be pointing back to your design time config. (e.g. Warning with a message "Unable to read configuration file...").

Another diagnostic is to print out ("fire info events") your connection strings, presuming these are part of your SQL Server configuration, as a preliminary script task. This information will tell you right away if configurations are being properly overlaid.

Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dim conn As ConnectionManager

For Each conn In Dts.Connections
Dts.Events.FireInformation(0, String.Empty, String.Format("Connection: {0} DataSource: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)
Next conn

End Sub|||

I ran accross that as well and thought I was sunk. Turns out it must be an old error and fixed at some point. (I'm running 2005 SP1).

We use an XML configuration in C:\MSSQL to define where the Configuration connection points to and pull all other entries from Configuration source. We just deploy an XML file to each developer desktop (that points to development) and on each of the servers (pointing to themselves). We can move packages from development to test to production with no issues as well as run them in debug mode on our local desktop. In the off chance you HAVE to run a production job in debug from your desktop, just replace your local XML file (and make sure you run with appropriate permissions).

To be more clear, our configuration looks like:

XML Configuration File
SQL Server

This seems to work well for us. Hope this helps,

Larry C

|||

Larry & jaegd,

Thank you for replying to this thread.

Ok, the package has two connection managers, 1. ConfigurationConnection used for the SQL based configuration connection 2. DW_ETL is used for the DataFlow and other tasks in the package.

Using the script from jaegd to log the connection strings for all of the connection managers in the package I have found that the connection managers' connection strings are being changed appropriately to their configured values as stored in the SQL based configuration.

However, I am seeing, and this is the observation that caused me to post here, that a package variable User::TestConfig is not being changed appropriately. The script is listed below. The variable User::TestConfig is specified to get its value from the SQL based configuration.

Am I missing something here, shouldn't the variable's value be changed to what is specifed in the confguration table?

Public Sub Main()

Dim conn As ConnectionManager

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

For Each conn In Dts.Connections

Dts.Events.FireInformation(0, String.Empty, String.Format("CONNECTION: {0} DATASOURCE: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)

Next conn

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

Dts.TaskResult = Dts.Results.Success

End Sub

-Eric

|||

Wanted to update this thread.

I have confirmed, that a user variable that has its Value stored in configuration is not being properly updated from SQL based configuration. This happens when the package is run via a SQL Server Agent job. However, the Value is changed correctly when the package is run interactively through BIDS.

Is there some sort of caching mechanism in SSIS (just a swag) that is causing the variable's Value to NOT change even though it is set up to be configured from SQL based configuration.

As a reminder other information such as connection string which are stored in SQL based configuration are changing based on their configured values, this happens correctly in a SQL Agent environment as well as BIDS.

Thanks!

-Eric

|||

Larry,

I am curious, what environment are you running on where the xml and SQL based configuration is working properly? The following is my environment.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

(No column name) (No column name) (No column name)
9.00.2153.00 SP1 Enterprise Edition (64-bit)

I am asking because using xml config to point the table-based configuration's connection string is not working for me. Are there any gotcha's regarding the connection managers, or the job step configuration, or....

In the package I specified the order of the package configurations with the xml config listed first, then the sql based config. In the job step I had added the location of the xml-based configuration in the configurations tab. I did not have any check boxes checked on the Data Sources tab in the job step. The configuration data itself was correct. But, the sql based configuration was not being pointed to the connection string that is specified in the xml config. The logging output showed that the sql-based connection string was changed, however the values that were specified in the sql-based configuration were not being updated.

Any tips or help would be most appreciated.

-Eric

|||

Eric,

Here's the values you requested.

(No column name) (No column name) (No column name)
9.00.2047.00 SP1 Enterprise Edition (64-bit)

As a validation I did the following:
I created an SSIS package
I created a Connection called Configuration using integrated security pointing to the Development server and SSIS configuration database.
I created a variable called Test and set the value to Test
I Enabled configuration
I created an XML configuration file at C:\MSSQL\Configuration.xml (I'd recommend using a default extension)
I put Configuration data source in the XML file
I named the Configuration XML
I added a new SQL Server Configuration, specified server, table, and Key
I chose to store the configured Value of the Test variable
I enabled Logging to a file C:\MSSQL\Logs\Sample.log
I added a Script Task
I set ReadOnly variable to Test
In Design Script I put the following

Public Sub Main()

Dts.Log(Dts.Variables("User::Test").Value.ToString, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

End Sub

I enabled logging for the Script Task and enabled the ScriptTaskLogEntry

Important items:
The XML configuration file must be on a local hard drive
The developer will need an XML configuration file pointing to development
Each server will need an XML configuration file pointing to itself and the configuration database.
The database and table the configurations are stored in, must be what is referenced in it's local XML configuration file.
When creating an XML configuration for a new package Reuse the existing XML file.

The output from running on my desktop and the development server was:
User:ScriptTaskLogEntry,IT-HQ4Z971,CHARLOTTE\lcharlton,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{2179265C-2855-4A02-962F-4D1D5E2F27E0},11/1/2006 2:22:47 PM,11/1/2006 2:22:47 PM,0,(null),Test

The output from running on the production server was
User:ScriptTaskLogEntry,xxx-xxx-xx,CHARLOTTE\xxxxxxxxxx,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{FC1A8FF0-B4D4-4604-98C6-F5BD60C2DEE3},11/1/2006 2:27:57 PM,11/1/2006 2:27:57 PM,0,(null),Production

The configuration connection is getting reset correctly and the variable from configuration is comming from the production server. We have equally good luck setting other connections to different databases. I even have a SSIS package that updates the configuration entries for another SSIS package and then executes it, allowing us to iterate through a list of servers collecting information. This works as expected in Development, Test, and Production with the only thing needing to change being the configuration values in the SSIS database.

|||

Larry,

Thank you for the time and response in the validation of this. The description of your implementation including the "Important items:" helps me validate that we are attempting to do the same thing.

So, your version is the SQL Server 2005 Service Pack 1 build, x64, while my version is the Cumulative hotfix package (build 2153) for SQL Server 2005 build, x64.

I thought I recalled that this issue was fixed in SP1. I am wondering if this was "un-fixed", for lack of a better word, in the cumulative hotfix package?

I am thinking this may be the case.

Any ideas beyond taking this to PSS?

-Eric

|||Sorry, nothing here. PSS is probabbly your best bet.

deploying packages

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

WTF? ·%$·&$%%& i`m sure my connections are ok,, cause i prevoiusly checked them, the problem occurs when deploying the package.. it seems to be an error when connecting to the source but whyy?, and what about the package configurations ? should i use them,, and how?

Does the error message occur while you 'deploy' them or when you try to execute them after deployment. I am assuming 'deploy' at the least means they are now on another computer from where you created the package. Did you modify the package property 'protectionlevel' before you deployed? If not the default value may be causing your issues and you will want to read about the protectionlevel property in books online.

Hope that helps.

|||

in my solution proyect works fine.. so i have the deployment utility folder, then i copy it to the target computer, the error appears when i'm using the wizard to deploy it.. i`ve changed the protection level and it remains the same error... which is the correct option?

sorry if my english sucks..

|||

apparently i′ve found a possible solution.. in the project properties on the security tab.. i stablished a package password, then skept the prevalidation of the package during the deployment, once in my target server i run the package giving the password package and bingo!!! it worked.. i don`t know if this is the most viable solution but it worked anyway.. please can anybody tell me if this is the best option?

Deploying multiple files through a package

Hi

I am working on on SQL Server Integration Services and facing few problems in it.

Actually am supposed to create a package that would automatically pick excel spreadsheets with a specific format and import it onto the SQL server.
(Lets say , there is a company named AB and they have got various products named CD,EF and GH and each product has its own spreadsheet in which its monthly sales(JAN, FEB,...NOV, DEC) is given. So i have to build a generic package for each product (lets say CD) so that i don't have to import every spreadsheet seperately for each month.
To summarize i just have to build a package where i can deploy the various spreadshhets again and agian instead of building a package for each and every month spreadsheet seperately.

I have tried and used lots of combinations like Loop
conatiners etc. but still am not able to find a solution to it.

Please help me out on this.

If the metadata (i.e. Number of columns, column names, column types) of each file is different then you cannot load everything using the same data-flow. You need a seperate data-flow for each one.

You can still put everything into one package. You need to decide, at execution-time, which data-flow(s) to execute based on which file you want to process. In other words, you need conditional workflow: http://www.sqlis.com/default.aspx?306

-Jamie

|||

But the format of all the monthly spreadsheets are the same. So still i have to cosider conditional flow or it can be done through any other way also.

Cheers

|||

Oh OK, well you're all right then. You just need to set the name of the file that you want to process dynamically. You do this by putting a property expression on the Excel Connection Manager's ConnectionString property.

-Jamie

|||

Hi

I am really sorry to bother again but actually ,as i am new to these services, can you please guide me how to do it or may be direct me to some useful article.

Thanks a lot .Its been really helpful though.

Cheers

|||

Sure, this: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx provides all that you need to know.

There's probably more in there than you need but you can pick out the bits that are important. Make sure you download the demo which will show you how all this works.

-Jamie

|||

Thanks a lot. I really appreciate it. Its working fine now.

Actually i have twisted the previous question a bit. I don't know why but i am just keen to learn whatever comes my way.

Lets say we have a database table(eg XYZ) with certain attributes (A,B,C,D,E,F) and now we want to build a package which would take excel file(eg XUV) as a source and export the data onto the table which has already been created in the database(i.e XYZ). But in the excel file we only have few attributes that match the destination column(lets say A,C,F). Now in order to get it working we have to map the columns manually. Then perform the normal procedures and it would be loaded.

This package would deploy only one source file(i.e XUV). Actually what i was curious about is that if there are hundreds of excel source files(with the same attributes like the former excel source XUV) waiting to be loaded then am i supposed to do mapping for each and every file.

Thanks in advance.

|||

Not quite sure I fully understand what you're saying but the important thing is you have to build a data-flow for each type of source file. By "type" I mean all files that have the same metadata are of the same type.

-Jamie

|||

Hi

Actually am still facing some problem with my last query.

There is no connection manager in the property listings in the expressions.

|||

Where are you looking? What object are you looking at the properties of?

You have to change the ConnectionString property of the connection manager.

-Jamie

|||Really sorry was a mistake.

Deploying IS properly

Hi,

How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?

thanks a lot

cherriesh

With DTExecUI you can build command line that can be executed using DTExec in a job see

Here another article about it|||

cherriesh wrote:

Hi,

How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?

thanks a lot

cherriesh

A short answer could be yes.

You can deploy SSIS packages to a file system folder or store them in a DB. You could also use the 'deployment utility' that is part of BIDS, but there are some know issues with that (e.g. deploying a config file used by multiple packages).

If you are going to deploy the packages to multiple environments; make sure you use 'package configurations' to set some property' values at run time dynamically. For more info:

http://technet.microsoft.com/en-us/library/ms137900.aspx

deploy SSIS package to server

Ok, I created SSIS packages on my local box. All of my packages are using config files for the db connections and other configurations that'll be changed per environment. My question is how do I deploy the .dstx file and the associated config file to the servers?

Right now I'm running the packages in BIDS as I create them. I now want to run them on an actual server

One way is to map a drive (or just open a remote folder) to the server, and then copy the files there.

I suggest you create a folder on the server to hold the SSIS stuff. From there you can create subfolders if you desire.|||

would I need to create folders for each package or no? How will the SSIS package now what config file is assocaited with it?

|||

IGotyourdotnet wrote:

would I need to create folders for each package or no? How will the SSIS package now what config file is assocaited with it?

You don't need to create a folder for each package, though that decision is up to you.

In the package (control flow, right click, package configurations), you specify which configuration file to use.

Deploy SSIS package to production which has a connection to oracle databse

Hi There,

In SSIS package development environment, I was able to connect to an oracle database and pull data into my sql server database. I installed the client tools for oracle and I put an entry into the tnsnames.ora and I was able to connect.

But in production environment, if I deploy the package on sql server, I was wondering if I had to do the same job of downloading the oracle client tools onto my production machine --which creates a tnsnames.ora file to it default location and then edit it with tthe tns entry-- or is there a better way to do this--avoiding the download?

Can somebody plzz help me ?

Thanks.

You need to install the Oracle Client (9i) or (10) on the actual SQL Server box.

This is for the network libraries. There is a special build of the client for Windows 2003 Server if you go to Oracles site and have a look around

cheers Dave

|||

You do need to repeat the same steps like you've done in your development environment: install Oracle client and adjust tnsnames.ora file.

There are 2 more options:

1) There is another "lighter" option - Oracle Instant Client:

http://www.oracle.com/technology/tech/oci/instantclient//index.html

2) The Oracle Data Provider for .NET (ODP.NET) . It has Oracle client bundled with it.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

Regards,
Yitzhak

|||

Hi Yitzhak,

If I intstall the lighter option, where do I adjust the tnsnames.ora file? I don't find the file after installation. Do I need to manually create it and put it in a specific location? Can you please reply back?

Thanks.

Sam.

|||

Also, which of the instant client packages should I download? There are 5 downloads for install client for 64bit.

Thanks.

Sam.

|||

Oracle has Instant Client FAQ page:

http://www.oracle.com/technology/tech/oci/instantclient/ic-faq.html#A5028

How do I ensure that my "tnsnames.ora" file is being used in Instant Client?
Always set the TNS_ADMIN environment variable or registry setting to the full path of the tnsnames.ora file. This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

Regards,
Yitzhak

|||

Because you are using MS SQL Server 2005 SSIS on Windows OS, just 2 options are applicable for you:

Windows Itanium 64-bit 10.2.0.2|||

HiYitzhak,

Can you please bare with me? I downloaded the windows x86 64 bit install client and this is what I did.

I unzipped the folder contents to c:install\instantclient

I created a tnsnames.ora file with the tns entry under 'C:\'.

I added the full path 'c:install\instantclient\' to the path environment variable

I also added a tns_admin environment variable giving it a value 'C:\tnsnames.ora'.

Now I test executing the package using dtexec and I still can't connect to the oracle database. It still gives me an error 'Client and networking components not installed. Am I missing smth?

Can you please help me?

Thanks.

|||

There is a command line tnsping.exe Oracle utility to check both client installation and tnsnames.ora settings.

You can try to launch it at the command prompt:

c:\>tnsping oracle-instance-name

Regards,
Yitzhak

Deploy Problem

I created a package, created an XML Config file, created a deployment utility and then deployed to server.

Changed Connection Managers Data Source and Initial catalog properites during deplyment. Config file in the deployment folder have the right data source and initial catalog properties value.

But when i run the package..I see wrong connection string(still showing dev server name) even though I changed during these properties during deployement and shows it right in configuration file in deployment folder.

From where the package is reading connection properties....very confusing

I will appreciate any help!!!

Amar

Don't use the data source and initial catalog properties. Use the ConnectionString property instead. At least, that's worked good for me.|||

Thanks Phil for you reply.

I have used Data Source and Initial Catalog properites in another pacakge and it worked fine...but not this time;)

|||

I even tried ConnectionString property..but still not working. I am missing something here

|||Are you sure that when you deploy the SSIS package can find the *correct* config file? It's not grabbing values from a duplicate config file sitting somewhere, is it?|||

SSIS package should use config file in deployment folder...i dont think SSIS is doing that. I cannot find any other config file.

Anything else i should check.

|||

When you right-click on the background of the control flow, do you have the XML Configuration file listed under "Package Configurations"? If yes, what directory does it point to?

Are you receiving any errors when you open up the package on the second server?

|||

Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder.

No..I am not getting any error on the second server.Just the connection string is wrong.

|||

Amar Khaira wrote:

Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder.

No..I am not getting any error on the second server.Just the connection string is wrong.

Here's the deal. I believe you *need* to specify a path. Here's why. When I opened up and saved a new XML configuration file without a full path, my config file got stored in: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE.

Then I deployed the package, and opened the package from the deployment folder (C:\Documents and Settings\Phil Brammer\My Documents\Visual Studio 2005\Projects\Test01\Test01\bin\Deployment), and the values were picked up from the IDE folder, not the deployment folder.

Check into that and use absolute paths, where possible.|||I can confirm Phil's answer, we ran into a similar problem recently. To use XML config files without the full path, you have to make sure the current working directory is the same as the directory to package is located in. If you are running DTEXEC directly, you could use a batch file to change the directory (CD) before calling DTEXEC. If you are running it from SQL Agent, the only work-around we found was to have Agent call a batch file to run DTEXEC instead of running it directly.|||

I specified path while creating XML config file...but it still does not work after deployment.I am deploying the package in File System.

But here is what i noticed. When I open package from deployment folder by clicking C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Some_FolderName\Package_Name.dtsx it shows right connection properties.

But I try to run the package from IS by right clicking the package->Connection Manager it shows wrong server name.

I am doing something terribly wrong. Please HELP!!

Amar

|||Unless you have the full SSIS client installed, when you right-click and execute a package, it is using your local machine's properties, not the remote machine's.|||I have full SSIS Client Installed.