January 29, 2022 at 5:15 am
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.
Thank you
January 29, 2022 at 5:52 am
January 29, 2022 at 4:55 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2022 at 6:27 pm
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
January 29, 2022 at 6:29 pm
Google is your friend
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.
January 29, 2022 at 7:59 pm
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.
January 29, 2022 at 11:11 pm
issue resolved. please ignore post.
January 30, 2022 at 12:17 am
issue resolved. please ignore post.
January 30, 2022 at 5:08 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 30, 2022 at 5:39 pm
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