Run SQL Query in C#

  • Hi

    I am working with SSIS to automate data collection.  I need to make a SQL query call inside C#.   The query takes in one input parameter and outputs a number.

    1.  How would I make the call in C#?
    2. Does it matter if I create a function or stored procedure for the query in question?

    Thank you

    • This topic was modified 3 months, 3 weeks ago by  water490.
  • Curious - why do you need to create a script task and execute a SQL query in that script task?  If this is a one-time call to get/set some variable(s) then an Execute SQL Task would be much better.

    If this is being done in a data flow - then that code would be called for every row in the pipeline, which is not going to be very efficient.

    There are probably (likely) better ways of solving the problem - but we would need to know what you are trying to accomplish.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Curious - why do you need to create a script task and execute a SQL query in that script task?  If this is a one-time call to get/set some variable(s) then an Execute SQL Task would be much better.

    If this is being done in a data flow - then that code would be called for every row in the pipeline, which is not going to be very efficient.

    There are probably (likely) better ways of solving the problem - but we would need to know what you are trying to accomplish.

    Thanks for the reply.  Good question for sure.  I am using SSIS to automate data collection from a data service provider.  I need to make sure that any file that I download from the SFTP site is new.  The way I check if a file is new is to compare the file name of the file at the SFTP site against a SQL Server table.  If the file is found then the file is not new hence it should not be downloaded.  This process will happen every day.

    The biggest performance hit is going to be the initial time I run the SSIS job because I will be downloading decades of daily data.  After this is done then it will be downloading daily files which will be one or two files.

    Does this help answer your question?

    Thank you

  • DesNorton wrote:

    Google is your friend

    https://www.google.com/search?q=c%23+exec+sql+stored+procedure+with+parameter&rlz=1C1GIGM_enZA726ZA726&oq=c%23+exec+sql+stored+procedure+with+parameter&aqs=chrome.0.69i59j0i22i30l3j69i58.256j0j7&sourceid=chrome&ie=UTF-8%5B/quote%5D

    I rely very heavily on Google for sure.  I find that it is a good tool but it doesn't replace guidance from people who know what they are doing.  That is why I come to this forum because this forum has a lot of very knowledgeable people.

  • Jeffrey Williams wrote:

    Curious - why do you need to create a script task and execute a SQL query in that script task?  If this is a one-time call to get/set some variable(s) then an Execute SQL Task would be much better.

    If this is being done in a data flow - then that code would be called for every row in the pipeline, which is not going to be very efficient.

    There are probably (likely) better ways of solving the problem - but we would need to know what you are trying to accomplish.

    this is part of a process the connects to a ftp server, gets list of files on it and needs to check if it is a new file or if the file has already been downloaded - so it will be called on the middle of a c# script (I supplied it on another thread) - so using a SQL task would not work here.

  • issue resolved.  please ignore post.

    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
  • issue resolved.  please ignore post.

    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
    • This reply was modified 3 months, 3 weeks ago by  water490.
  • If I couldn't keep every file in a local folder - so that I could use a simple File.Exists() in C# to determine if the file has already been downloaded, then I would pull the full list of files into a lookup component in SSIS.  One call to the database to get the full list - one call to the FTP server to get the directory list, then a foreach over the list of files from FTP and a lookup to determine if that file has already been downloaded.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    If I couldn't keep every file in a local folder - so that I could use a simple File.Exists() in C# to determine if the file has already been downloaded, then I would pull the full list of files into a lookup component in SSIS.  One call to the database to get the full list - one call to the FTP server to get the directory list, then a foreach over the list of files from FTP and a lookup to determine if that file has already been downloaded.

    the c# code that is using winscp is doing just that - retrieving the list of files on the remote server - only the files that match a particular pattern in any of the sub folders (which have names that are unknown to the process) - and it is a lot easier and makes more sense to have it on a single block of code.

    open sftp session

    -- get list of remote files

    -- foreach file

    ---- check if file is new -- against sql server table

    ------ download if new -- add entry to sql server table

    -- endfor

    -- close sftp session

    lot easier, clearer and maintainable than to have 1 ->script task to get list of files -- 2 -> recorset to hold list of ALL remote files - 3 -> Loop container(over recordset) 4->sql command to check if file exists -5-> script task to download single file - 6->sql command to update table with list of downloaded files

Viewing 10 posts - 1 through 9 (of 9 total)

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