Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: SQL 2010 SSI Help

    Hello all!

    My name is David and I am new to SQL2012 / SSIS and these forums. I have tried to do this with just knowledge on the web and Internet. I am having some disconnects between what they are saying and what is shown and how to do it unfortunately. SQL and SSIS are not my strong suit, I am a VMware guy.

    So here it goes...

    Simple Integration Services the idea is to take two flat file CSV's and use the full name to sort both files, then take the data in the rowes from file A and put them in the associated rows in file b example.

    File A
    Full Name Employee ID Job title Department description Office Location ( take that data and update the corresponding rowes below)
    John Smith 554 sr system admin CBP Civilian Chantilly VA

    Name Description Title Department Location
    John Smith 554 sr system admin CBP Civilian Chantilly VA

    The reason we are not just renaming the headers is because this file contains other lookup data for the application it is getting imported back into using a power shell script after the data transfer. It seems so simple but i just cant figure it all out. Any help will be appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First order of business, you can mix and match SSIS and PowerShell to your heart's content. You don't need to do one or the other.

    I don't understand enough of what you're trying to accomplish to help you very much... SSIS is a full blown data management platform. It might not be as elegant as some of the big name players, but it is faster, smaller, and at least for me it is easier to comprehend. I have no doubt but what it can do what you need, but I'm not sure that it is the tool that I'd choose for this task...

    This is a bit like using an Abrams to drive nails... You can do it, as long as the nails are very close to the ground. It is a bit more challenging to drive the same nail on the roof of your house using an Abrams. At least in my experience, a hammer does a nicer job for both cases!

    Can you provide a more specific example of the two files you have (include four cases, two with exact matches, one in only one file and one in only the other files). This will let us see what you are trying to do and how you want to handle problems. Maybe after we can see what you want, then we can offer a better analysis and answer for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    2

    info

    I agree with you and have considered moving to another solution but SSIS will work with nicely with AD and that part i have already done.

    Here you go file one from ADP Workforce now.

    File 1
    First Name | Last Name | Home Department Description | Job Title | Position ID
    Nabil Ali Dept of State Engineer BD1000431
    James Daout DHSS OSC Sys Admin BD1000476

    File 2
    Name | Department | Description | Job Title
    Nabil Ali DoS 17: Civ 431 principal engineer
    James Daout 43 DHSS OSC TS 476 Sr System Admin Principal

    File 1 is a report generated from ADP that contains updated employee data, file 2 is the destination of the employee data. I want first name and last name to go into a field named full name which i have done and tested on SSIS, then i need the position id transformed to the last 4 digits and converted into a number field to remove leading zeroes which I have done. The look up value should be the full name so after file 1 has had the transformations I want Nabil Ali to look at the other file for Nabil Ali and change/ write to the 3 fields (Department) , (Description), and (job title) I will be adding more but if I know how to get those three working I can figure out from there how to get the other fields. After all this I have custom scripts written to write the data into AD. The reason I need the info moved to File 2 is because File 2 contains the look up info for Active Directory as well as other key attributes. Anyway to accomplish this will work, I just need the output to be File 2 not a new file generated and file 2 to remain a CSV with the other fields intact.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Get-ADUser commandlet ought to do what you have described, but moving on to SSIS.

    1. Create the two SSIS data sources: one from your CSV file and the other from Active Directory.
    2. Massage the data as needed within those data sources.
    3. Join the data sources in the SSIS package. Make sure to handle failed matches appropriately.
    4. Output the result of the join to the new CSV file.
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •