How to accomplish what I am trying to do?

Hello,

I need to write a package the does the following, and not sure the best approach:

The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.

That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.

Also, each time I create the new output file, I need to delete the old named file.

I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.

Any help appreciated!!

Thank you

You can use an expression on your flat file connection manager to change the file name at runtime. Just set the ConnectionString property. To replace the file, make sure your Flat File destination has Overwrite data enabled.

Do all your stored procs return the same resultset? If not, using a data flow will probably not work for you, so you wouldn't be able to use a Flat File destination.

|||

Hi John,

You say that if my procs do not return the same result set a data flow will probably not work?

What can I use instead?

I am having serious problems trying to get the output from my stored procedure loops to flat files.

That is, I added a data flow task to the foreach loop, so that each execsql task will run a stored proc then write the output to a file. But it seems this is not possible?

I find it hard to believe... but is there a workaround?

|||You could use a custom script task inside your loop to execute the procedures, read the results, and write them to a file.

How to accomplish this?

I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.

I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:

Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed

In the end I want to create two tables like so:

Table 1:
Event A / Closed
Event B / Modified

(Event C is not here because it was voided, so any prior records of C are gone).

Table 2:
Event A / Property A / Closed
Event A / Property B / Created

(Only events with properties would be in this table).

In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.

Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.

"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).

I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).

I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.

So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?

Thanks for your help.May be you need to move the 'modifies' logic to a separate dataflow after the 'creates' are done. I don't think there is a easy way of enforcing the order of parallel data pipelines in a single dataflow.|||

Hi StGeorge,

I think you need to create some mechanism by which to order properties. I trust you when you say your actual process is more complicated, so I may be headed down the path with this suggestion - but here goes:

It sounds as if you are adding a row to Table 2 on a state named Created. It also sounds as if you are removing this row from Table 2 and its parent row in Table 1 on a similar state named Void.

Would it work if you created an integer column in Table 2 called PropertyOrder? You could populate it with 1 on Created, 3 on Void, and 2 for any other property event. In SSIS, you could then load the records from the source ordered by this field. If your transactional environment is intense (lots of asynchronous transactions), you may be forced to stage the ordered data (in a table or raw file) and then act on this resultset in a subsequent data flow or Execute SQL Task to guarantee transactional integrity (which is how I interpret Table 1).

I may be way off here. If I am, please accept my apologies.

The method I propose above is roughly analogous to a method many deterministic network protocols use to guarantee packets are output in the order sent. The packets are generated synchronously, transmitted asynchronously, received in any order, cached, and reconstructed by ordered key before delivery. I think you have all the pieces except the key by which to order the records.

Hope this helps,
Andy

How to Accomplish This

If I have the following Data in a Table:
TranType Qty
1 10
1 20
2 15
3 7
What is the best way to accomplish this?
Type1 Type2 Type3
30 15 7
Thanks,
KFKeith,
if you've fixed trantype ie limited to 3 then following query will solve the purpose otherwise you
will have to
make use of dynamic crosstab using T-SQL.
--sample table and data.
create table #t(TranType int,Qty int)
insert into #t select 1, 10 union all
select 1 ,20 union all
select 2 ,15 union all
select 3 ,7
--Required query.
select sum(case trantype when 1 then qty else 0 end) type1,
sum(case trantype when 2 then qty else 0 end) type2,
sum(case trantype when 3 then qty else 0 end) type3
from #t
Refer to following url for dynamic crosstab solution.
http://www.sqlteam.com/item.asp?ItemID=2955
--
- Vishal|||"Vishal Parkar" wrote
.
.
> Refer to following url for dynamic crosstab solution.
> http://www.sqlteam.com/item.asp?ItemID=2955
Save yourself a lot of time and aggravation
and check out the RAC utility for all kinds of
crosstabs and pivoting:)
RAC v2.2 and QALite @.
www.rac4sql.net

How to Accomplish MySQL-style LIMIT

I've graduated from MySQL to MSSQL Server, but the LIMIT command, which was very useful in MySQL isn't in MSSQL. There's the top command, but that doesn't really help me with my DataGrid paging needs. So how do I go about simulating the LIMIT command in MSSQL?

I tried searching the forums, but I couldn't seem to find anything...This link might be helpful|||Cool. Thanks. That was most helpful. I actually searched the forums for "paging," which returned a lot more results, but this was the best. Thanks again!

How to accomplish creating a simple calculated column in a table

Is there a way to create a calculated column in a table like this, or is
there a better way to accomplish this task? I've tried, but SQL keeps
adjusting my precedence and it goofs up the formula:
Let's say the column name is "NetPrice". I want to deduct the discounted
amount from the total to arrive at the net price. Discount is a decimal
value, ie: 25% = .25.
[TotalPrice] - ([TotPrice] * [Discount])Oops, typo below. Should be: [TotalPrice] - ([TotalPrice] * [Discount])
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Bill
See if this helps
CREATE TABLE #Test (
TotalPrice DECIMAL, Discount DECIMAL,
NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
SELECT * FROM #Test
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Thanks Uri. I think I was entering something incorrectly when I originally
entered the formula into the table designer.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePsmpBmnGHA.764@.TK2MSFTNGP03.phx.gbl...
> Bill
> See if this helps
> CREATE TABLE #Test (
> TotalPrice DECIMAL, Discount DECIMAL,
> NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
>
> INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
> SELECT * FROM #Test
>
> "Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
> news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
>

How to accomplish (Date)Between in MDX?

Hi all,

I thought it would be simple, but it turned out to be to difficult for me. I have the next problem;

I want to get the salesinfo of the latest 30 days, It should show me all the data between _Now() minus 30 days and Now()_
Has anyone any Idea how this could be accomplished?

Regards.

The main trick here is how to map the result of the now() function to a particular member in the date dimension.

Could you tell us how your Date dimension is structured and give some examples of what you are using for the key and name for the day attribute.

Also, are you sure that you want to use now() - another common approach would be to find the last day with data and then go back 30 days. A lot of data warehouses get re-populated overnight, so they are always lagging atleast one day behind the live data.

|||

Thanks for your reply Darren,

My Time Dimension is structured as follow:
Year (1-1-2005, 1-1-2006)
Quarter (1-1-2006, 1-4-2006)
Month (1-1-2006, 1-2-2006)
Week (1-1-2006, 8-1-2006)
PKDate (1-1-2006, 2-1-2006)- this is the Key

The reason why I want to use Now() is that the Time Table in the Datamart has fixed dates until the year 2008. And I'm not able to change this because I'm only working on reporting, and we work with subscription which have an enddate in 2007 or 2008.

Hopely this clearifies it a little bit.

Regards.

|||

I'm still making some assumptions, but I think you could set up a calculated member roughly like the following.

Create member CurrentCube.Time.PKDate.Today as StrToMember("[Time].[PKDate].[" + format(now(),"d-m-yyyy") + "]")

Then your measure for the last 30 days would be:

Create member CurrentCube.measures.Last30Days as Aggregate([Time].[PKDate].Today:[Time].[PKDate].Today.lag(30))