Showing posts with label table1. Show all posts
Showing posts with label table1. Show all posts

Sunday, March 11, 2012

Change values in SSIS.

Howdy all. In DTS there was a way too change the values of a column during
and export to another table using ActiveX Script:
Source:
Table1
column1
column2
Destination
Table2
column1 (I want this to contain the exact values from Table1.column1)
column2 (I want this to contain the values of Table1.column2 + 10)
Basically you would do this through the mappings, instead of a Copy Column,
you would use an AxtiveX script for that column. How can this be done in
SSIS?
TIA, ChrisRThanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5FAF7EA7-D0E7-41FB-AF37-B70DA73AE95E@.microsoft.com...
> Hi Chris
> You can use a script component as a transform task see
> http://www.sqlis.com/default.aspx?307,1
> John
> "ChrisR" wrote:
> > Howdy all. In DTS there was a way too change the values of a column
during
> > and export to another table using ActiveX Script:
> >
> > Source:
> > Table1
> > column1
> > column2
> >
> > Destination
> > Table2
> > column1 (I want this to contain the exact values from Table1.column1)
> > column2 (I want this to contain the values of Table1.column2 + 10)
> >
> > Basically you would do this through the mappings, instead of a Copy
Column,
> > you would use an AxtiveX script for that column. How can this be done in
> > SSIS?
> >
> > TIA, ChrisR
> >
> >
> >
> >
> >

Friday, February 24, 2012

Change SQL Command at runtime?

I have tried some of the fixes for the other posts but it still doesn't work.
My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?Use an expression as your source. Note that once you do this in the dataset
tab that you can no longer execute the query or get the field list returned
so make sure you have the appropriate field list before you change it to an
expression.
="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
Parameters!myParam.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
>I have tried some of the fixes for the other posts but it still doesn't
>work.
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?|||How do I set the source? This query is a very large query with many other
parameters being used to narrow down the search and return data. I use a
text command type in the data tab.
"Bruce L-C [MVP]" wrote:
> Use an expression as your source. Note that once you do this in the dataset
> tab that you can no longer execute the query or get the field list returned
> so make sure you have the appropriate field list before you change it to an
> expression.
> ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> Parameters!myParam.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> >I have tried some of the fixes for the other posts but it still doesn't
> >work.
> >
> > My query looks like this:
> > SELECT *
> > FROM table1 AS t1, table1 AS t2
> > WHERE t1.id =t2.id + @.myParam
> >
> > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> >
> > How can I make this work?
>
>|||Ok I have it figured out. I left the existing parameters setup as is and
just put a =" at the beginning of the query, a " at the end, and took out all
Carriage Returns and it works. Thanks for all of your help!
"KCBTDev" wrote:
> How do I set the source? This query is a very large query with many other
> parameters being used to narrow down the search and return data. I use a
> text command type in the data tab.
> "Bruce L-C [MVP]" wrote:
> > Use an expression as your source. Note that once you do this in the dataset
> > tab that you can no longer execute the query or get the field list returned
> > so make sure you have the appropriate field list before you change it to an
> > expression.
> >
> > ="SELECT * FROM table1 AS t1, table1 AS t2 WHERE t1.id =t2.id " &
> > Parameters!myParam.Value
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "KCBTDev" <KCBTDev@.discussions.microsoft.com> wrote in message
> > news:95A60AE1-12C4-4CF3-9A82-B037D9E57BFB@.microsoft.com...
> > >I have tried some of the fixes for the other posts but it still doesn't
> > >work.
> > >
> > > My query looks like this:
> > > SELECT *
> > > FROM table1 AS t1, table1 AS t2
> > > WHERE t1.id =t2.id + @.myParam
> > >
> > > I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> > >
> > > How can I make this work?
> >
> >
> >

Change SQL command at runtime using a stored procedure?

My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?
I did have my report working correctly with my source as an expression. But
with added complexities to the query, it will be easier to maintain in a
stored procedure.Sounds like you need to research dynamic sql
"KCBTDev" wrote:
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?
> I did have my report working correctly with my source as an expression. But
> with added complexities to the query, it will be easier to maintain in a
> stored procedure.