SSIS question

Hi Everybody

My doubt is

I am pulling a file list using Execute SQL Task. The query that I am using is

 SELECT        File_Sequence_Number , FileName 
 FROM            AC.ARC_File_List

and I selected the ResultSet as FullResultSet and I am passing it to User::FileList

After this I am using a ForEach loop container. Here I used variable mappings as follows

 User::FileSequenceNumber with Index 0
 User::ARCFile with Index 1

In the loop first I have to check for Earlier data feed and for that I have to get the substring of the FileName that I am pulling into the User::FileList

I am using SQL Execute Task but I am not able to understand how to access the FileName from User::FileList, so that I can get the date from it and compare it with dates that are available in other table in the DB.

I have been trying to do this from two weeks. Please somebody give me a solution


more ▼

asked Mar 08, 2012 at 08:53 PM in Default

avatar image

1 1 1 1

I tried Parameter mapping but the query gives me the error. The code that I used is SELECT CASE WHEN ((CAST(((MAX(CAST((SUBSTRING(ARCFileName, 9, 4) + SUBSTRING(ARCFileName, 5, 2) + SUBSTRING(ARCFileName, 7, 2)) AS DATETIME))) - (CAST((SUBSTRING(?, 9, 4) + SUBSTRING(?, 5, 2) + SUBSTRING(?, 7, 2)) AS DATETIME))) AS INT)) > 0) THEN 'YES' ELSE 'NO' END AS IsEarlierDate FROM AC.Fact_ARC_Activity WHERE (ISDATE(SUBSTRING(ARCFileName, 9, 4) + SUBSTRING(ARCFileName, 5, 2) + SUBSTRING(ARCFileName, 7, 2)) = 1) GROUP BY ARCFileName, ?

The error that I get is [Execute SQL Task] Error: Executing the query "SELECT CASE WHEN ((CAST(((MAX(CAST((SUBSTRI..." failed with the following error: "Each GROUP BY expression must contain at least one column that is not an outer reference.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am not sure as how to use the aggregate function and not keep the other column name Group By

Mar 08, 2012 at 10:56 PM MadhaviPasapula

@MadhaviPasapula Sorry, but it sounds too complicated. There seems to be two parts of your question. First, pertaining to SQL and second pertaining to SSIS. For SQL part can you please provide details of the tables involved, some sample data and the desired output? Then the SSIS part would become very easy.

Mar 09, 2012 at 04:54 AM Usman Butt

One of the tables that I use has the dates of the files that are loaded into the DB. I have to compare the Max of the dates with the parameter values but I cannot use Parameter in the Group By. I am not sure as to how to compare a max date with a parameter and not use Group By.

Mar 09, 2012 at 02:43 PM MadhaviPasapula
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first


I guess I resolved it. I added one more component to calculate the maximum value of the date and then passed it as a parameter to the next component. This way I dont have to use the aggregate in the other component and so GroupBy is not required.

more ▼

answered Mar 09, 2012 at 07:22 PM

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 08, 2012 at 08:53 PM

Seen: 988 times

Last Updated: Mar 09, 2012 at 07:22 PM

Copyright 2017 Redgate Software. Privacy Policy