November 12, 2014 at 12:21 am
Hi Friends,
Actually i have Flatfile as a TXT files .In that i have 100 Columns and i need to import those datas in to SQL Server 2012 by using SSIS package.
As my Manager instructions we need to add a new column in the Flat-file as well as Database.In database i have added the new column name as FtpDate and Datatype as DATE(Purpose of this column is to know on which date we have loaded the particular Source File).
Now in Flatfile we cannot add manually .we need the add it dynamically Because these flatfiles are coming different location from the world.
We are getting those files through FTP and storing into the Local Folders and then we start the process by calling the Local path.
My Problem is how to add a dynamic column name (FtpDate) into the flatfile Source.
After Added the new column (FtpDate) into the database i tried to run the package and i got the Error which i mentions below.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Any one Please suggest me how to do this.
I am waiting for your Answers friends !!!!
November 12, 2014 at 12:36 am
arunnrj87 (11/12/2014)
Hi Friends,Actually i have Flatfile as a TXT files .In that i have 100 Columns and i need to import those datas in to SQL Server 2012 by using SSIS package.
As my Manager instructions we need to add a new column in the Flat-file as well as Database.In database i have added the new column name as FtpDate and Datatype as DATE(Purpose of this column is to know on which date we have loaded the particular Source File).
Now in Flatfile we cannot add manually .we need the add it dynamically Because these flatfiles are coming different location from the world.
We are getting those files through FTP and storing into the Local Folders and then we start the process by calling the Local path.
My Problem is how to add a dynamic column name (FtpDate) into the flatfile Source.
After Added the new column (FtpDate) into the database i tried to run the package and i got the Error which i mentions below.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Any one Please suggest me how to do this.
I am waiting for your Answers friends !!!!
For a given file, how do you derive/calculate the FTPDate?
Do you really want to manually edit these files before they are processed? That sounds inelegant and non-performant to me.
Or can you take the existing flat file as input and add a new FTPDate column to the dataflow (probably using a Derived Column), to be output to the target date?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2014 at 12:47 am
Hi Phil,
Thank you for your Reply.
I will calculate the columns FTtpDate from the file name and the Filename will be like this format
''ACTEST00001_20141111_0408_INV.TXT''
''ACTEST00001_20141111_0408_SL.TXT''
''ACTEST00001_20141111_0408_SV.TXT''
''ACTEST00001_20141111_0408_SV_APPT.TXT''
And we have details in this files.
But i dont have column FtpDate in these files.
I have this column in Database.
So i need to derive the data from the file name and put into the columns
Yea i need to take the Existing flat file as input and add a new FtpDate column to the dataflow and to be output to the target date to the destination.
November 12, 2014 at 12:56 am
There is an advanced property in the flat file source where you can add a column that contains the filename of the flat file you are loading.
FileNameColumnName property, Flat File Source Adapter : SSIS Nugget
Using a derived column you can extract the date from the filename and store it in your database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 2:05 am
Koen Verbeeck (11/12/2014)
There is an advanced property in the flat file source where you can add a column that contains the filename of the flat file you are loading.FileNameColumnName property, Flat File Source Adapter : SSIS Nugget
Using a derived column you can extract the date from the filename and store it in your database.
+1 this is exactly how I would do it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2014 at 2:11 am
Hi pls can any share any videos regarding this because i am new to this process.
November 12, 2014 at 2:17 am
arunnrj87 (11/12/2014)
Hi pls can any share any videos regarding this because i am new to this process.
Which process are you referring to?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 2:19 am
To do the entire process.. I tired as u told but its getting same error.
November 12, 2014 at 2:27 am
Actually U got my problem right ??
November 12, 2014 at 2:39 am
There may be error messages posted before this with more information about the failure.
Please post all of the error messages.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 2:45 am
[SSIS.Pipeline] Warning: The output column "FtpDate" (598) on output "Flat File Source Output" (6) and component "Flat File Source" (2) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
[Flat File Source [2]] Error: An error occurred while skipping data rows.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
These are the Warning and Errors i am getting..
November 12, 2014 at 2:50 am
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
November 12, 2014 at 2:58 am
How did you add the column FtpDate to the source component?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 3:03 am
I have added by going to DataFlow Task -->Flatfile Source -- >Right Click Show Advanced Editor-->
Component Properties -- > Under Custom Properties-->In FileNameColumnName--> i have five the Column Name as FtpDate and then Clicked Ok
Thats all i have done.
November 12, 2014 at 3:10 am
arunnrj87 (11/12/2014)
I have added by going to DataFlow Task -->Flatfile Source -- >Right Click Show Advanced Editor-->Component Properties -- > Under Custom Properties-->In FileNameColumnName--> i have five the Column Name as FtpDate and then Clicked Ok
Thats all i have done.
This is not the way we suggested. Leave the flat file source untouched. Add a derived column after the flat file source.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply