SSIS package design for chking updation loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS package design for chking updation loop

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-26 : 02:50:23

Hi All,

I am developing package, my requirement is that picking up max date from excel1 and pass it into sql query and check whather it exists into DB. if yes then aviod updation loop and if not then go to update loop and do updation.
In another loop i am checking template name and max date from excel2 and for each template and max date if template name and max date is exists then avoid updation loop and if not then go to updation loop and update the data coming from excel2.

Currently i am using for 1st loop (from this excel1 i am getting only one max date)
1.Execute sql task get max data from excel1
2.Store the max date into variable Cum_MaxDate as Object
3.pass this into For each loop editor Collection as User::Cum_MaxDate, Variable mapping User::Cum_MaxDate -- Index as 0
4.In For each loop i am using Script Task to convert this max date object into date variable
5.Pass this date into another Execute Sql Task
Select count(*) CNT from dbo.Cum_Temate -- table into DB
where (Date = ?)
6.Using this CNT into constained Editor to decide wheather to follow updation loop or not.

For 2nd updation loop
1. I am retriving max date and temate name from excel2 using Execute Sql task (connection type as excel2) as
Select MAX(Date) as Max_Date, Temate as Maxx_Temate from [TemateData$]
where Temate is not null
group by Temate

But here onwards i am not able to proceed..as i am getting 5 record here for two columns (Max Date and Temate)
Plz help me out to proceed.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 05:34:00

from what I understand what you need is this

1. Start with ForEachloop with file enumerator to loop through excel files which you'll be putting in a folder
2. Add execute sql task inside loop which fetches maxdate and template name of iterated excel through OPENROWSET and put them onto your variables. This is set based and doesnt require parsing entire data inside excel
3. check in another sql task count of records in db table for this template and date. set a bit field value based on this condition
4.check value of bit field and also precendence constraint using expression and constraint option and link your update task to this

this will automatically do full tasks for any number of excel sheets present in the folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-26 : 05:41:37

thank u very much did exactly and got it...
need small help for C# code. i m getting temate names A,B, and C
and want to pass them to Execute Sql Task into below format
('A','B','C')

I am currently using below code as

Object column;
string strcoln_Name;
column = Dts.Variables["temate"].Value;
strcoln_Name = column.ToString();
Dts.Variables["temate_out"].Value += "'" + strcoln_Name + "',";
MessageBox.Show(Dts.Variables["temate_out"].Value.ToString());

But i am getting as 'A','B','C',
dont need extra comma at end...
T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 05:53:36

where are values stored currently? in a object variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-26 : 06:04:27

Yes,

i am storing in temp table...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 06:41:38

then isnt it enough to use below query for generating list?
SELECT STUFF((SELECT ',''' + Value + ''''
FROM #Temp
FOR XML PATH('')),1,1,'') AS ListValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-27 : 02:04:37

Hi,

Thank you for your reply..i am getting exactly what i needed..but now the problem is with the next task..i am passing these values ('A','B','C') into SQL Execute task (Excel connection type) to get the excel values having Temate in ('A','B','C') like (Select * from [Temate$] where Temate in (?)...but i am getting no records here...passing individual values i am getting values for Select * from [Temate$] where Template in ('A')....i need these record data set for updation purpose in for each loop.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 03:26:38

you need to form sql string for that in your execute sql task.
make a new variable to store query, keep EvaluateAsExpression property as true and set expression as below

"Select * from [Template$] where Template in (" + @[User::Variable] + ")"

Where @[User::Variable] is previous variable used to store the comma seperated list generated above.

Then in execute sql task make source type as from variable and map it to new variable created above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-27 : 06:40:40

hi..really helped one
i am getting still error..what i did ..i defined
execute sql task
1.Result Set - single row
2.SQL statement: SELECT STUFF((SELECT ',''' + Value + ''''
FROM #Temp
FOR XML PATH('')),1,1,'') AS ListValue
3. Result Set => Result Name -- ListValue
Variable Name => max_Dat {max_Dat variable as string}
In next execute sql task
Connection type- Excel
Result Set - Full
Sql Source Type - Variable
Source Variable- User::Query {Query - "Select * from [Template$] where Template in (" + @[User::max_Dat] + ")"}
Parameter mapping- User::max_Dat Direction-Input Parameter Name-0 Parameter size - 500
Result set-Result Name - 0 Variable name- Excel_Result {Excel_Result as object}

i am getting below error in first execute sql task itself

[Execute SQL Task] Error: An error occurred while assigning a value to variable "max_Dat": "The type of the value being assigned to variable "User::max_Dat" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 06:46:12

did you try running the statement for max_dat and check if it returns values correctly?


SELECT STUFF((SELECT ',''' + Value + ''''
FROM #Temp
FOR XML PATH('')),1,1,'') AS ListValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-27 : 07:02:51

yes it is returns exactly like

('A','B','C')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 08:18:33

and max_dat is string also right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-28 : 01:33:45

yes..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 01:41:31

hmm...then didnt understand why it should show the posted error. are you doing variable mapping correctly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2011-12-28 : 02:38:05

Hi..Thank u for ur help

it got resolved as :)

SELECT CAST((STUFF((SELECT ',''' + Value + ''''
FROM #Temp
FOR XML PATH('')),1,1,'') AS NVARCHAR(500)) AS ListValue

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:32:10

oh ok...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources