December 12, 2007 at 10:46 pm
I am trying to query a SQL Server database table, extract a date and varchar field from the table, then use those variables to do another query, then create a flat file based on the variables (all within a foreach loop):
I setup my package in a similar fashion to that described in the following URL: http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
1) Execute SQL Task
a) Results go to ado object source variable.
2) Setup FOREACH loop
a) Setup variables that are mapped to the record set collection from 1(a). For instance,
variable name : CompanyName String value=0 NameSpace: User (index 1, as this is the second column in the recordset)
b) In a Data Flow Task (not sure, but perhaps my problem is not using another Execute SQL Task?), run the following query:
select * from dbo.mytable where Company = User::CompanyName -- @CompanyName? CompanyName.Value?
c) Feed that query result into a Flat File Destination where I hope to use the expression builder and the CompanyName variable (with some other string values) to create a flat file name.
If I do end up using a Execute SQL Task in step 2, what would I use as the destination?
Sorry... pretty new to SSIS here... the documenation (BOL and other) on variable use has been pretty sparse at best. I have tried to follow along in a book, Microsoft SQL Server 2005 Integration Services, but variable usage is not explained well there, either (IMO).
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
December 13, 2007 at 12:50 am
DB_Newbie2007 (12/12/2007) b) In a Data Flow Task (not sure, but perhaps my problem is not using another Execute SQL Task?), run the following query:
select * from dbo.mytable where Company = User::CompanyName -- @CompanyName? CompanyName.Value?
If you are using OLD DB Source then this statement should be:
select * from dbo.mytable where Company = ?
and pass your variable name as parameter with order equal to 0.
December 13, 2007 at 8:51 am
I found a website today that describes variable usage, http://www.sqlis.com/81.aspx .
However, when I have three "columns" of data coming in, index 0, 1, 2, (for the Column1, Column2, Column 3 data) how can I use the ? to access the Column2 and Column3 "variables"?
Unfortunately, I have structured the query such that I need to do the following (looping through the record set to divide up the data):
select * from dbo.mytable where Company = [Column2] and Date = [Column3]
Column1 is just a count() to get the data grouping. I can switch the order of the original query, but I still need to get access to that second column of data in the query.
I am beginning to think that just writing a T-SQL script might be easier! LOL
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
December 13, 2007 at 10:42 am
DB_Newbie2007 (12/13/2007)
I found a website today that describes variable usage, http://www.sqlis.com/81.aspx .However, when I have three "columns" of data coming in, index 0, 1, 2, (for the Column1, Column2, Column 3 data) how can I use the ? to access the Column2 and Column3 "variables"?
select * from dbo.mytable where Company = ? and Date = ? and Other = ?
first ? is parameter 0
second ? is parameter 1
third ? is parameter 2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply