First let me say, I really can't believe this chain of events myself--and they are happening to me.
I am upgrading several DTS packages to SSIS on what will be my new production server. These packages create tables, export them to a flat file, and ftp them off to other locations.
What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file. Then when my pickup/load routines run, the data is out of place and they fail.
Can anyone please explain what is happening here with the mapping. I have evaluated the table and the columns are in the order that I expect. When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily.
I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.
Thanks in advance for any help and/or information you can provide
SSIS Changing Column Order during Transformation
trufaux
Hello
It's regarding step# 4-Execute SQL Task (stored procedure to utilize command line SFTP).
I have come across a requirement to communicate (for receiving, deleting files etc) with an SFTP server from within a DTS Package.
Please let me know the solution to this requirement e.g. Stored Procedure.
It's an urgent requirement, and your help would be greatly appreciated.
Thanks,
Rahul
Asmodai
Why is this a problem Does it honestly matter that columns appear in a different order to which they appear in the source. I can understand that it would be helpful if they did but it hardly constitutes a major problem.
All you need to do is map the columns accordingly.
-Jamie
scott Hxxxx
Jamie,
I guess that the real issue that I have is, why What is causing the change in column location
Yes, I can change the load on the consuming end (these servers cannot not speak to each other directly...which would eliminate the issue). On the recieving end, I have an SSIS package which bulk loads this data into a table. The problem is that with the data columns out of order, the bulk load fails.
I could, and may have to, re-build, but I would really like to understand the root issue before proceeding down that road. The additional consequences of this kind of action will also be possible with other extractions that I automate utilizing SSIS (DTS) where I am not in control of the loading system and where column placement is absolutely key.
Thanks for answering, I look forward to hearing additional information from you and others.
CypHost
Even given what i said before, it does seem strange that the column order is changing.
Did you build the package using the wizard or manually
Which component causes the column order to change
-Jamie
awturnbull
Gil Danziger
If you can build a consistent repro of the problem when migrating your DTS package, it might be worth posting it here:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Thanks.
Al Longobardi
I initially built these with the wizard. The odd thing is that out of seven individual packages, this problem only affected three.
The steps are:
1-Execute SQL Task (script embedded as SQL STatement Source)
2-DTSTask_DTSDataPumpTask Export Data
Data Flow
a-OLE DB Data Source linked to the table which will be exported
b-TextFile Destination
***It is here that I can look at the OLEDB Source and see the columns in the correct order as expected, but also see the mapping go haywire
3-Execute Process Task (Zip Exported Flat File for Transport)
4-Execute SQL Task (stored procedure to utilize command line SFTP)
5-Transfer SQL Server Object Task (create a copy for other uses)
6-Transfer SQL Server Object Task (create a copy for other uses)
In short, Step 2 is where my problem surfaces and appears to be. All of the other steps work as anticipated.
Let me know if I can provide any addtional information.
Thanks