March 6, 2009 at 12:44 pm
Is there a simple way to get the SSIS flat file connector to automatically use column names when the incoming flat file(s) do not have column names in the first row?
I was trying the following (easy way out for a very large table?):
1. Get comma delimited list of column names to match the flat file columns:
DECLARE @ColumnName varchar(max)
select @ColumnName = ISNULL(@ColumnName+',', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
WHERE Table_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION
SELECT @ColumnName AS ColumnName
2. Save the result to a flat file that used the same naming convention as the data/flat file(s).
3. Create my SSIS Flat File connector, point it to the file from (2) above, select the COLUMN NAMES IN THE FIRST DATA ROW.
4. Save the connector.
5. Re-open the connector... deselect the COLUMN NAMES option, then re-save the connector.
Unfortunately, when I get to step (5) the names all default back to Column 0, Column 1, etc.
I am using some of the columns elsewhere in flow control tranforms so the column name(s) are important.
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 6, 2009 at 1:26 pm
Why not put actually put the column names in the first row of data.
Setup your query to get the column names from the schema view then union that single row of data with your actual data. Now of course all your columns will need to be character type.
Also not sure if your header row will always be on top but that could be solved easy enough. My guess if it is the first union input then it will be on top. - Just a thought.
February 5, 2010 at 1:13 pm
Did you ever find a resolution to this problem? I would like to do a similar task where I have a wide flat file that does NOT have column headings in the first row (and can't add them because it's from an outside vendor). However, I really don't want to take the time to change all the column names in SSIS manually. Thanks for your help.
February 5, 2010 at 1:51 pm
Unfortunately no, never found a solution for this. I did have to manually add each column name (and datatype) to the flat file connector.
One word of caution (just found this out this week, this unfortunately affected millions of my incoming records in 2009 :sick:), if you use a decimal datatype in the flat file connector, anything to the RIGHT of the decimal point will get truncated when inserting the data into SQL Server:
Use a NUMERIC datatype instead and use the SCALE for the items to the right of the decimal point.... :pinch:
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 5, 2010 at 1:58 pm
Thanks for the reply. I checked some of my old SSIS packages to see if I had used DECIMAL and I had not. I used CURRENCY (not really sure in hindsight but maybe it was because I came across this same issue). Again, Thanks!
February 12, 2010 at 7:55 am
After step #4 in the original problem, open View/Code from top line menu to look at the package XML, search for
"<DTS:Property DTS:Name="ColumnNamesInFirstDataRow">-1</DTS:Property>" and change it's value from -1 to 0.
Then go back and open the connection. The checkbox for "Column names in the first data row" will be unchecked but the column names will still be there.
Save the connector.
February 12, 2010 at 8:24 am
Charles,
Thanks for the update. I was just getting ready to post this myself... I just figured that technique out this week when I had some 250-row csv files I needed to process.
Here are a few more steps that may be helpful:
1) To generate a list of column names from the SQL table you are going to insert into, you can run the following. I used this and then pasted the result in the first line of my flat file (don't forget to remove it before you start your actual SSIS processing!!!!)
DECLARE @ColumnName varchar(Max)
SET @ColumnName = ''
select @ColumnName = @ColumnName+column_name+','
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'mytablenamehere'
order by ordinal_position
PRINT @ColumnName
2) Get the datatypes for each column (as far as I know this still has to be done manually). You need to subtract 1 from the Ordinal position as the SSIS columns start with Column 0 not Column 1...
select Ordinal_position-1 as Position, COLUMN_NAME, CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar)+')'
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN Data_type
END as DATA_TYPE
--select *
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'mytablename'
order by ordinal_position
I started work on trying to develop a XML-SQL Table schema generator to use to just paste into the SSIS dtsx project file, but that will be a "spare time" project. 😀
I used this to modify the datatypes for each column as appropriate.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
November 30, 2016 at 5:00 pm
Charles, thanks for this solution. I had a similar issue in Visual Studio 2012. What worked for me in View->Code was changing:
DTS:ColumnNamesInFirstDataRow="True" to DTS:ColumnNamesInFirstDataRow="False"
Going back to the Connection Manager, the box was unchecked, but the column names were still correct. I ran the package and all of my data rows loaded correctly. When I go back to View->Code, the DTS:ColumnNamesInFirstDataRow field is no longer visible as it would be if I left the box unchecked in the first place. This is perfect so thanks for the tip and thanks DB_Newbie for asking my exact question.
February 7, 2017 at 1:00 pm
charles.johnson-658597 - Friday, February 12, 2010 7:55 AMAfter step #4 in the original problem, open View/Code from top line menu to look at the package XML, search for "<DTS:Property DTS:Name="ColumnNamesInFirstDataRow">-1</DTS:Property>" and change it's value from -1 to 0.Then go back and open the connection. The checkbox for "Column names in the first data row" will be unchecked but the column names will still be there.Save the connector.
Thank you so much for this! It worked like a charm. I had multiple tables with 100 columns each to load and I could not possibly imagine manually creating the list of columns.
March 20, 2018 at 7:14 am
JustOffal - Tuesday, February 7, 2017 1:00 PMcharles.johnson-658597 - Friday, February 12, 2010 7:55 AMAfter step #4 in the original problem, open View/Code from top line menu to look at the package XML, search for "<DTS:Property DTS:Name="ColumnNamesInFirstDataRow">-1</DTS:Property>" and change it's value from -1 to 0.Then go back and open the connection. The checkbox for "Column names in the first data row" will be unchecked but the column names will still be there.Save the connector.Thank you so much for this! It worked like a charm. I had multiple tables with 100 columns each to load and I could not possibly imagine manually creating the list of columns.
My table has 1005 columns... this saved me a lot of time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply