Post reply

Need help, to load dynamic columns

  • January 7, 2011 at 7:21 am

    #1270652

    Mr Cozyroc, I accepted that I can't code -> NET, JAVA, PERL... etc. If you call it laziness in your language, you are most welcome.

    I do not have the code. I am a contractor. I will have to get in touch with people there and I will try my best to post the code.

    RUDE yes, I accept my post is rude because I felt that Phil's post was rude too.

  • January 7, 2011 at 7:43 am

    #1270674

    touchmeknot (1/7/2011)

    Mr Cozyroc, I accepted that I can't code -> NET, JAVA, PERL... etc. If you call it laziness in your language, you are most welcome.

    --

    After reading this, I am glad our 'languages' are different.

    Next time you pose a question, and I respond by telling you only that my mate recently solved something exactly similar in under 25 seconds while simultaneously eating pizza, I'll assume that you find it useful, because I'm speaking your language.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 7, 2011 at 8:04 am

    #1270683

    touchmeknot (1/6/2011)

    Phil, I never meant to offend you. People agree and disagree with many things.

    But kindly, do not post any reply if your reply discourages others. People post here when they are in need or when they need advice from experts and their ideas. Your post (atleast here doesn't help anyone).

    And Yes, I would love to nominate my colleague. Kindly read my first post. I wanted to load two excel files, problem is mentioned there. I managed it with a single DFT along with a script task. He helped me with with his code, without which it wouldn't be possible.

    As mentioned in my first post, I am not an expert but I tested the package and FYI, it is in production now.

    Phil wasn't rude. He responded to your initial question with the right answer:

    you simply cannot modify a connection manager in SSIS with expressions to upload files with different formats.

    Yes, you can do it by importing it as one column and do the logic in SQL Server.

    Yes, you can do it by programmatically extending SSIS by using .NET (be it a script task or a 3rd party component like CozyRoc)

    But it cannot be done with out-of-the-box SSIS.

    Phil also wasn't being ironic by asking the code for the community and giving the developer a medal.

    It is a very common request here on the forums and a lot of people will be helped by such a script task.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 8, 2011 at 4:47 am

    #1271042

    Chris

    "Remember SSIS is just a tool. Sometimes its better to get the data into SQL and then massage it."

    I completely agree. People try to use SSIS for everything. In order to solve something not standard you need to know VBscript. Why not learn VB.net and T-SQL and you are better off.

    Gosta M

  • January 8, 2011 at 9:40 am

    #1271057

    Gosta Munktell (1/8/2011)

    I completely agree. People try to use SSIS for everything. In order to solve something not standard you need to know VBscript. Why not learn VB.net and T-SQL and you are better off.

    That is a very wide generalization.

    I know TSQL and VB.NET, but for the ETL tasks, I will surely use SSIS just for the ease of development, the conditional logic that is easy to implement, the ease of expressions, the easy logging and event handling.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 8, 2011 at 10:43 am

    #1271064

    touchmeknot123 (1/7/2010)

    Hi,

    I have to load a csv file on monthly basis. The columns in the file are not fixed.

    example : Id, 200911,200910 might be the columns in one file

    Id, 200811,200810,200809 might be the columns in the other file

    I have to go the properties of the file connention manager and click 'Reset Columns', works fine.

    1) Is there any way to use the script task and have the columns 'Reset'.

    2) I googled and found that we can use a script task and can load the columns dynamically.

    Can any one provide me with steps and code for the same. I can't do complicated coding with script task.

    Thanks in advance

    I don't know how to dynamically get a script task to do the equivalent of clicking "Reset Columns" especially since I don't work with SSIS.

    I do, however, know how to solve problems like this in T-SQL. Can you attach a file and provide the CREATE TABLE script for the target table? Of course, the file should not contain any personal, private, or company sensitive information.

    As a bit of a sidebar, my first recommendation would be to press the provider of the data to give you the data in a properly normalized format.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2011 at 8:21 pm

    #1271100

    I appreciate your help Jeff.

    I am sorry, I realize that I have mentioned Excel (in my other posts) which I have posted after the first one.

    I managed loading the csv's. I do not have those csv's with me (as I am a contractor and I left the other company long ago). If you see the first post, it was in Jan 2010.

    However, if you still want to play with the csv, you can create one and I can help you create it. Please let me know, if the information in the first post isn't enough.

  • January 8, 2011 at 11:30 pm

    #1271105

    touchmeknot (1/4/2011)

    Hello everyone,

    I agree with Craig and disagree with Phil.

    I succeeded in loading the file. Yes, it seems impossible but I succeeded in loading the file dynamically without using any third party tool.

    I took help of a .NET developer and he coded the script task for me, in about an hour :). The credit goes to him. I had to accommodate two excel file in a single DFT, depending on the file received by the external team.

    edit : I have mentioned excel here, it was a csv file.

    Yeesh. I'm not entirely sure what I said that was different then Phil other than discussing workarounds. If you'll notice the end of said post, I end up agreeing with Phil's direct comment. It can't be done internally to SSIS.

    All I did was mention there are alternatives to SSIS, a direct load and manually building the file converter being one of them. Well, to each their own.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 9, 2011 at 8:24 pm

    #1271232

    touchmeknot (1/8/2011)

    I appreciate your help Jeff.

    I am sorry, I realize that I have mentioned Excel (in my other posts) which I have posted after the first one.

    I managed loading the csv's. I do not have those csv's with me (as I am a contractor and I left the other company long ago). If you see the first post, it was in Jan 2010.

    However, if you still want to play with the csv, you can create one and I can help you create it. Please let me know, if the information in the first post isn't enough.

    I totally missed that. I'm still mentally stuck in 2010 and this is 2011. All the recent posts are in January 2011 and I just flat out missed the extra digit.

    Thanks for the feedback. I appreciate the offer of help to create the CSV file to play with but, after doing the same thing a couple of times in real life, I don't need the excitement of play on this one. 😀 I was looking to help you but I'm a year too late. :hehe:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply