March 26, 2015 at 6:28 am
Hi to all. I was unsure which form to post to for this question so as the backend is SQL 2014 I opted for this one 🙂
I created an ssis package to output a file which is then sent by FTP. This all worked fine when the flat file connection was set to 'Delimited'. I was then asked to change to 'Fixed Width'. This generates error below.
[Flat File Destination [2]] Error: The column name for column "My Column" could not be written. The column name may be longer than the available column size.
[SSIS.Pipeline] Error: Flat File Destination failed the pre-execute phase and returned error code 0xC0202095.
The OLE DB Source is a simple query:
SELECT 'C2' AS [Company Code],LEFT(JobID,20) AS [Project Code],LEFT(JobDescription,30) AS [Project Description],'Job' AS [Project Type],
'Y' AS Active,CONVERT(nvarchar(30),effectiveDate,103) AS [Start Date],'' AS [End Date], '' As [Revised End Date]
FROM JOBS
WHERE Jobs.SystemType = 'F'
I am using Visual Studio 2013 Shell.
I have spent a fair few hours on this issue now. I tried adding a 'Data Conversion' task but this did not have the desired effect. I am no closer to finding a resolution.
Can any of you gurus help me on this one?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 6:42 am
The error message suggests that the problem is with the output, not the input.
What is the column width, in the output file spec, for column 'My Column'?
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.
March 26, 2015 at 7:00 am
Hi Phil. It defaulted to values InputColumnWidth = 1 OutPutColumnWidth = 1 (the output value for this column was Y). Manual update made no difference and the values defaulted back.
I have been having a play and have managed to finally get the package to run. I deleted the original Flat File Destination task. I created a new one then selected option for Flat File Format as 'Ragged right' (previously this was set to 'Fixed Width'). Ragged right states 'The columns are defined by fixed widths, except the last one which is delimited by the new line character'.
Thanks for taking a look at my post. I value any advice.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 9:38 am
OK so now I have a new issue. When I import the package into SQL Integration Services then create a job to run the package it fails to FTP the file created.
Error
Message
Executed as user: DOMAIN\Account. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:27:55 Error: 2015-03-26 15:27:56.31 Code: 0xC001602A Source: Package Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed End Error Error: 2015-03-26 15:27:56.31 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:27:55 Finished: 15:27:56 Elapsed: 0.375 seconds. The package execution failed. The step failed.
Can anyone help with this one?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 10:02 am
Are you using Don't Save Sensitive as the package isolation level and then passing the password to the package as a 'sensitive' SSISDB environment parameter?
If not, try that.
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.
March 26, 2015 at 10:09 am
Hi Phil.
Are you using Don't Save Sensitive as the package isolation level and then passing the password to the package as a 'sensitive' SSISDB environment parameter?
I am using 'Don't Save Sensitive Data'.
Regards 'passing the password to the package as a 'sensitive' SSISDB environment parameter' I have NO IDEA how to implement! 🙂
How might a novice approach such a task?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 10:26 am
Have a read through of this[/url] Stairway article and see whether you can begin to get to grips with the use of SSISDB environments, then feel free to post back.
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.
March 26, 2015 at 10:29 am
Thanks Phil. I will take a look but do not hold out much hope!
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 10:35 am
2Tall (3/26/2015)
Thanks Phil. I will take a look but do not hold out much hope!Phil.
It's a really cool way of managing config settings in SSIS 2012+. Including sensitive data, like passwords.
It's not that complicated, once you get into it. Don't give up 🙂
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.
March 26, 2015 at 10:37 am
I may sleep on it and revisit tomorrow as my head is spinning 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2015 at 10:39 am
2Tall (3/26/2015)
I may sleep on it and revisit tomorrow as my head is spinning 🙂Phil.
It's been that sort of a day here too. I'm going for a cleansing ale to rejuvenate.
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.
March 26, 2015 at 10:43 am
I have a Blandford Flyer on ice...
Enjoy your Ale 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 27, 2015 at 4:33 am
I have made some progress (rightly or wrongly).
Steps:
1) I created Integration Services Catalog
2) I imported my package
3) I configured my package (Connection Managers > FTP Connection Manager > Added server password
4) Created a job then ran the package.
This worked (rightly or wrongly).
I could not work out how to use a variable to pass FTP credentials.
I now have another issue in that the format output is incorrect.....
My SSIS Flat File Connection Manager is configured as follows:
Format = Fixed Width
Header Row Delimiter = {LF}
Header Rows to Skip = 0
I have attached the output format + added the required format.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 27, 2015 at 6:08 am
I now have it working. The resolution.
Flat File Destination > Flat File Connection Manager >
ConnectionString = C:\pathFormat = Ragged right
Expressions = System Variables 'MyVariableName' (for path location)
To run as a scheduled job.
1) I imported the package into SSISDB. I then selected 'Configure'.
2) On 'Connection Managers' I selected my FTP Connection Manager then manually entered the server password
3) I configured a job.
Within SSISDB I also configured an Environment (under Environments) > variables. I configured this to be my FTP Connection. I thought I might be bale to reference it instead of performing step 2 above but could not see away.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 27, 2015 at 6:14 am
Are you using the project deployment model, or the package deployment model?
You need to use the former if you want to use environments. If you are not, I recommend that you learn about 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.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply