none
can i read a git repository file in an ssis pkg RRS feed

  • Question

  • Hi we run 2017 std. We may have a need to read the latest version of a file from a remote git repo.

    The best I can tell, a file task cant open such a repo as a source in a file copy operation.   For grins I entered much of the url path in a web service task's connection "server url" box but got a 401 (unauthorized) error return code when trying to connect.

    If memory serves, one could retrieve a file without coding a .net script in ssis from tfs.  But maybe I'm wrong.  Is there a way to read a file into the ssis buffer from a git repository?

       
    Monday, November 11, 2019 1:42 PM

Answers

  • I haven't seen such scenario so far in my working experience. 

    What I have seen is - Modify the code and push these latest code files like PowerShell in a repo, deploy them to shared folder or in some server using Azure devops and then use them in SSIS package. (File tasks points to a folder path)


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Monday, November 11, 2019 6:15 PM
  • thx r u saying one alternative is to use azure dev ops to deploy things like ps scripts to the file system where our scheduling system would then know its picking up the latest?

    More details on what I was mentioning:

    Currently in Production - SQL job -> SSIS  - Execute process task (calls a PS script) 

    If PS code has a bug, we create a local branch in repo, add modified code to this branch, push to master, azure devops deploys this PS file from master branch to test and prod server. Existing file is overwritten.

    Then SSIS automatically picks up this latest script. But this process is part of deployment.

    I am not sure how you are planning to check if code matches and abort job if it doesn't match and the purpose of it


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Monday, November 11, 2019 9:22 PM
  • Because some scheduling I just put together takes a new approach anyway, and because the repo is different, and because I sometimes go against the grain, I am thinking the opposite, ie bring down the job if they differ. 

    I would not add this process in SSIS i.e. check if file in repository matches with file in file system task. 

    Rather it should be - someone who modifies the code and pushes the latest file in repository, is responsible to deploy this latest file in test and prod server file system.


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Tuesday, November 12, 2019 1:59 PM
  • thx, devops looks like a whole culture. Is there a specific part of devops perhaps that I can try out to migrate some items? And then kind of grow into the culture?

    Please check below for reference - how DB deployment can be done using devops. 

    https://www.mssqltips.com/sqlservertip/5985/continuous-database-deployments-with-azure-devops/

    Build pipeline - builds the database solution stored in repo and dacpac is generated
    Release pipeline - deploys dacpac to test, prod servers. 

    Similarly, to copy files - Copy file task can be used (Source - repo file path, Target - server file system path)

    Please check what works best for your requirement.


    Regards,
    Vaibhav


    Tuesday, November 12, 2019 6:09 PM
  • vaibhav's info is very valuable and interesting but the original question remains, can I read a git repository item from an ssis pkg?  Unfortunately, I have to weigh all the alternatives and this is an alternative.

    A suggestion that would need a bit of research. 

    A Script Task that runs .Net or Execute Process that runs PS code to check file contents of Git file with file in File system task, return a variable if it matches. 

    You may post questions on .Net and PS forum separately get help in coding.


    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:03 PM
    Friday, November 15, 2019 3:33 PM
  • this guy says no (at least without an install) to c# which to me means no to any kind of off the shelf .net interface. I'll post updates here if I learn of anything different.

    https://social.msdn.microsoft.com/Forums/en-US/4c402287-ef6c-4726-bfb4-08fad5450ce8/can-i-extract-git-repo-files-from-c?forum=csharpgeneral


    • Marked as answer by db042190 Tuesday, November 19, 2019 12:03 PM
    • Edited by db042190 Tuesday, November 19, 2019 12:13 PM clarity
    Tuesday, November 19, 2019 12:03 PM

All replies

  • I haven't seen such scenario so far in my working experience. 

    What I have seen is - Modify the code and push these latest code files like PowerShell in a repo, deploy them to shared folder or in some server using Azure devops and then use them in SSIS package. (File tasks points to a folder path)


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Monday, November 11, 2019 6:15 PM
  • thx r u saying one alternative is to use azure dev ops to deploy things like ps scripts to the file system where our scheduling system would then know its picking up the latest?

    we trigger ssis from sql agent via ps via a scheduling system.

    I'm thinking the opposite, ie abort the scheduled job if the code doesn't match.  A little safer? 

    Monday, November 11, 2019 8:04 PM
  • thx r u saying one alternative is to use azure dev ops to deploy things like ps scripts to the file system where our scheduling system would then know its picking up the latest?

    More details on what I was mentioning:

    Currently in Production - SQL job -> SSIS  - Execute process task (calls a PS script) 

    If PS code has a bug, we create a local branch in repo, add modified code to this branch, push to master, azure devops deploys this PS file from master branch to test and prod server. Existing file is overwritten.

    Then SSIS automatically picks up this latest script. But this process is part of deployment.

    I am not sure how you are planning to check if code matches and abort job if it doesn't match and the purpose of it


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Monday, November 11, 2019 9:22 PM
  • Hi db042190,

    Please refer to How to link a SQL Server database to a Git repository.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 12, 2019 9:00 AM
  • makes sense Vaibhav, I'll have to read a little on azure dev ops. I believe its a migration tool and am already wondering if it is useful when all the items to be migrated are on prem. I like learning new things.

    Anyway, if i'm not mistaken, the standard at my new job is that prior to every run something has to happen to ensure that source control is in sync with the file system's source ps (etc) files.  I believe in the legacy items, we simply overwrite the file system files prior to eac h prod run.  Not sure yet.

    Because some scheduling I just put together takes a new approach anyway, and because the repo is different, and because I sometimes go against the grain, I am thinking the opposite, ie bring down the job if they differ. 

    I can see a few ways to compare files, one would involve ssis extracting both and then perhaps 1) a c# script or 2) executing something like windiff from within ssis or even 3) a merge.  The funny thing about the whole concept is "whats checking if the checker is the most recent version?". 

    Tuesday, November 12, 2019 1:18 PM
  • thx Mona. The best I can tell is that your link describes ways to save sql server scripts etc to source control perhaps thru some specific route that can be set up using what looks like 3rd party software.

    This post asks how an ssis pkg can read any git repo item (file), whether its sql or not.  In my case it happens to be powershell scripts. 

    Tuesday, November 12, 2019 1:33 PM
  • Because some scheduling I just put together takes a new approach anyway, and because the repo is different, and because I sometimes go against the grain, I am thinking the opposite, ie bring down the job if they differ. 

    I would not add this process in SSIS i.e. check if file in repository matches with file in file system task. 

    Rather it should be - someone who modifies the code and pushes the latest file in repository, is responsible to deploy this latest file in test and prod server file system.


    Regards,
    Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:04 PM
    Tuesday, November 12, 2019 1:59 PM
  • thx, devops looks like a whole culture. Is there a specific part of devops perhaps that I can try out to migrate some items? And then kind of grow into the culture?
    Tuesday, November 12, 2019 2:41 PM
  • thx, devops looks like a whole culture. Is there a specific part of devops perhaps that I can try out to migrate some items? And then kind of grow into the culture?

    Please check below for reference - how DB deployment can be done using devops. 

    https://www.mssqltips.com/sqlservertip/5985/continuous-database-deployments-with-azure-devops/

    Build pipeline - builds the database solution stored in repo and dacpac is generated
    Release pipeline - deploys dacpac to test, prod servers. 

    Similarly, to copy files - Copy file task can be used (Source - repo file path, Target - server file system path)

    Please check what works best for your requirement.


    Regards,
    Vaibhav


    Tuesday, November 12, 2019 6:09 PM
  • vaibhav's info is very valuable and interesting but the original question remains, can I read a git repository item from an ssis pkg?  Unfortunately, I have to weigh all the alternatives and this is an alternative.
    Friday, November 15, 2019 1:55 PM
  • vaibhav's info is very valuable and interesting but the original question remains, can I read a git repository item from an ssis pkg?  Unfortunately, I have to weigh all the alternatives and this is an alternative.

    A suggestion that would need a bit of research. 

    A Script Task that runs .Net or Execute Process that runs PS code to check file contents of Git file with file in File system task, return a variable if it matches. 

    You may post questions on .Net and PS forum separately get help in coding.


    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    • Marked as answer by db042190 Tuesday, November 19, 2019 12:03 PM
    Friday, November 15, 2019 3:33 PM
  • thx, I can probably try that. I can execute powershell from an exec process task assuming ps knows how to reach a git repository.   I'll post results back here.

    1) so far it looks like a git exe has to be available somewhere for ps to read a git repo.  That's my initial takeaway from https://stackoverflow.com/questions/16613998/running-a-powershell-script-from-c-sharp-to-pull-git-repository  .  I don't have an exe yet, I haven't needed one and I think I don't want one if it isn't necessary.  I've gotten away so far with a combo of using a web browser to define, see, upload, clone locally etc my repo.  And I've relied on some of the magic available in VS that allows me to commit locally and then push to the remote repo.  I don't see a natural interface capability between ssis and the remote git repo so far.   

    Friday, November 15, 2019 4:03 PM
  • Hi db042190,

    Please give your suggestion here.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 19, 2019 9:57 AM
  • this guy says no (at least without an install) to c# which to me means no to any kind of off the shelf .net interface. I'll post updates here if I learn of anything different.

    https://social.msdn.microsoft.com/Forums/en-US/4c402287-ef6c-4726-bfb4-08fad5450ce8/can-i-extract-git-repo-files-from-c?forum=csharpgeneral


    • Marked as answer by db042190 Tuesday, November 19, 2019 12:03 PM
    • Edited by db042190 Tuesday, November 19, 2019 12:13 PM clarity
    Tuesday, November 19, 2019 12:03 PM