Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

package with a custom component (log provider) runs in BIDS, but doesn't run where deployed.

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls? I'm really stuck.

Many thanks in advance,

Tamim.

Please search this forum...

You'll need to set the package protection level to "DontSaveSensitive" and then issue a password via the /SET command line switch. OR, you could try to promote to the server using SQL Server users/role level security, maybe....|||

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

|||

Tamim Sadikali wrote:

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

What do you mean that didn't work? That error message states that you are using indicates to me that the protection level is set to EncryptSensitiveWithUserKey, which means that ONLY the user who built the package can execute it.

Package Validation Error in Custom PipelineComponent

Hi

I'm developing an PipelineComponent (ComponentType.Transform). When I try to execute this component, the following error is thrown:

Code Snippet

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [DTS.Pipeline]: Buffer Type 1 had a size of 0 bytes.

Error at Data Flow Task [DTS.Pipeline]: The buffer manager failed to create a new buffer type.

Error at Data Flow Task [DTS.Pipeline]: The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0.

Error at Data Flow Task [DTS.Pipeline]: The layout failed validation.

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

(Microsoft.DataTransformationServices.VsIntegration)

Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Can anyone help me, please? The "Integration Services Error and Message Reference" didn't help me much.

Thank you

Manuel Bauer

The error does not give me any clues other than it happens during a validation stage, so what is in your Validate method?

Have your tried debugging this, i.e. Visual Studio attached to the execution host?

|||

Hi

Thank you for your answer. My pipeline component's validate method runs without any problems, I debugged this in design time and in run time (before the exception occured). In my opinion, the problem is the early validation (http://sqljunkies.com/WebLog/knight_reign/archive/2005/04/20/12365.aspx) of the data flow task. As I expect, the data flow task does any validation itself.

I can't imagine what "The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0." means, so I really don't know where the problem could be.

The exception is thrown immediately after running the package (also after running the validate method of my pipeline component).

|||

Does the package work if you take your component out of it?

The early validation vs late validation is the same Validate method calls, and since it generally works, I'd say it is clear that your transform has a problem, regardless of if it is the early or late call. Early and Late means normally Validate gets called twice, there is no difference between them. The Task will have a validate method, and a Data Flow will then call Validate for each child component.

How can the exception be thrown "after" running a package, Execute will never get called if a task fails validation. What is the return of your Validate method when you debug it?

Did you debug against dtsdebughost or just Visual Studio (designer)? For simplicity when trying to resolve run-time issues I set the code project debug option to use dtexec directly, just using /F and a package file. It is faster than attaching to a designer instance of VS.

|||

I found a solution for my problem. I was thinking wrong about this validation thing, I thought that the data flow task does some independant, general validation itself.

In my component, I "forgot" to call base.Validate(), which would no be a problem if the own validate method is implemented as id should. In my case, it was not.

Thank you for your competent analysis of my problem.

Manuel Bauer

Package fails with 0xC0047062

Background:
I have a package that reads data from an SAP table and populates a SQL table.
The package was developed using a custom source component I created to connect to SAP. The package runs with no errors in the Business Intelligence Development Studio.
I deployed the package to my test SSIS server successfully using a package password.
The Run Package option runs the package with no errors.

Problem:
I created a Job under the SQL Server Agent and configured a single Step to execute my package. This fails with the following error:

Code: 0xC0047062
Description: System.InvalidCastException: Unable to cast object of type ...
Description: component "SAP DB Source" (281) failed validation

The cast is fine as far as I can tell, so I'm not sure why this error occurs. I found other reports of this strange error message, but no solution. Any help is greatly appreciated!

Philip

Hello,

Have you ensured the custom components and dependencies are deployed on the target server besides the package?

sql