Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need help building a cube

hi,

i am a newbie to Analysis Services. Could someone help me build a cube. I read somewhere that a cube must contain some sort of measure or aggregate, however, i am working with data that has no need for a measure, it doesn't have columns that could be used as a measure(i.e. units or currency)...how can i build a cube without a measure and is this possible?

thanks

yadgor2000

Yadgor - without a measure there's nothing to cube up. What kind of data are you trying to analyze via a cube? I'd also highly recommend Harinath & Quinn's SSAS book if you're trying to get up to speed in Analysis Services...it's incredibly useful for me.|||

May be Ur not getting the actual facts in your data base...first prepare your dimensional modeling....then think again. Or U should take advice from Sr. Database guys in Ur organisation regarding...

Thanks

|||As per the other replies: If you have no measures, you have nothing to analyse... so the question to be answered is why are you trying to build a cube? Maybe some other technology would be more appropriate.sql

Need dynamic SQL

Hi all,

I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to
implement a logic to receive an adress build out of various user definable
fields from various user defined tables. The function is already implemented
in the Client software and as UDF-compliant in MySQL and Oracle. Now there's
just MS-SQL left...

The problem now is for sure, I'm in need of a scalar return value (a
varchar) composed out of a dynamic sql-context. The wohle informations are
placed in a definition table and would result in a sql-string like "select
@.cFieldlist from @.cTable where @.cWhere", e.g. "Select
title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+char
(13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10) from
customer where customer.tooffice=1"...

The problem is of course the calling of the dynamic sql statement within my
function. As read in different articles, this is impossible in MS-SQL. Is
there any possibility to workaround like creating a stored procedure with
output variables and call this within my function ?

Any hint is welcome
Ralphstored procedure with
> output variables and call this within my function ?

You can use OPENQUERY .|||[posted and mailed, please reply in news]

Ralph (Ralph.Backes@.web.de) writes:
> I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need
> to implement a logic to receive an adress build out of various user
> definable fields from various user defined tables. The function is
> already implemented in the Client software and as UDF-compliant in MySQL
> and Oracle. Now there's just MS-SQL left...
> The problem now is for sure, I'm in need of a scalar return value (a
> varchar) composed out of a dynamic sql-context. The wohle informations are
> placed in a definition table and would result in a sql-string like "select
> @.cFieldlist from @.cTable where @.cWhere", e.g. "Select
> title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+
> char (13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10)
> from customer where customer.tooffice=1"...
> The problem is of course the calling of the dynamic sql statement within
> my function. As read in different articles, this is impossible in
> MS-SQL. Is there any possibility to workaround like creating a stored
> procedure with output variables and call this within my function ?

You cannot call stored procedures from UDF:s. The idea is that a UDF
should not change the state of the database. Therefore you are severely
constraint in what you can do.

You can call extended stored procedures, though, so you could call
xp_cmdshell for do what you need from a second connection or a custom-
written XP for the task. Yura suggested OPENQUERY, and OPENQUERY is indeed
another loophole. But since OPENQUERY requires a constant SQL string,
you are still in a dead end.

And I would not really encourage the XP solution either. It may be
logicially correct, but performance will be poor. And if you use
xp_cmdshell there are security considerations. (And you have to be
really desperate to write your own XP.)

As I didn't really understand the problem, it's difficult to come
with suggestions, but you should probably use a stored procedure instead.
I have an article on my web site, which discusses the techniques
around this: http://www.sommarskog.se/share_data.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

Ralph (Ralph.Backes@.web.de) writes:
> So this piece of code should be called in the fieldlist, somehow like
> 'select customer.cust_no,dbo.adress('Bill',customer.cust_n o) from customer
> where deleted=0' and give in return a char (varchar(4000)?)like
> 'Mr.<cr>Hans Mustermann<cr>somewhere street 10 <cr><cr>55555 ZipLand'. To
> find the fields and the correct records, this code has to scan the
> definition table, read the fields to use and under which conditions to use
> and perhaps the tables which should be joined to the query, all written
> there.
> Is this really impossible ?

Generally, if you want to do something in an RDBMS, and also insist on a
certain syntax, it is not possible, unless you happen to pick the syntax
of the RBDMS in question.

For SQL Server, write a stored procedure that returns the address in
output parameters. If you need to handle many at a time, you can use a
temp table. See further here: http://www.sommarskog.se/share_data.html.

Sharing code between RBDMSs is very difficult, because there is a great
variety in what they support. It may simplify programming and maintenance,
to get the data to the client, using basic SQL which works in any RDBMS.
This may be not be good for performance, as more data will be shoveled
across the network.

Then again, your complex and dynamic data model is apparently not designed
for performance anyway. RBDMSs work from the idea that tables and columns
are stable, and that your schema does not change dynamically. So one idea
could be to have custom fields as rows rather than columns. This can lean
to unwieldy self-join, but at least you will not need dynamic SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952F32E37589Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Generally, if you want to do something in an RDBMS, and also insist on a
> certain syntax, it is not possible, unless you happen to pick the syntax
> of the RBDMS in question.
Misunderstanding, we don't insist on syntax, we insist on
functionality. The implementation is of course different on every
RDBMS. But til now every RDBMS gives us a possible way to implement
this stuff with more or less knowledge and doing. Hard to believe
MS-SQL can't...

> For SQL Server, write a stored procedure that returns the address in
> output parameters. If you need to handle many at a time, you can use a
> temp table. See further here: http://www.sommarskog.se/share_data.html.
A stored procedure isn't useful, I need a scalar return value... This
function is called within a reportengine and there is no other
processhandling than straight forward doing a select.

> Sharing code between RBDMSs is very difficult, because there is a great
> variety in what they support. It may simplify programming and maintenance,
> to get the data to the client, using basic SQL which works in any RDBMS.
> This may be not be good for performance, as more data will be shoveled
> across the network.
There is a lot of functionality on the client side, even the discussed
functionality is implemented at the client. But this really is a
dead-end for performance tuning, so a server-based solution is
necessary.

> Then again, your complex and dynamic data model is apparently not designed
> for performance anyway. RBDMSs work from the idea that tables and columns
> are stable, and that your schema does not change dynamically. So one idea
> could be to have custom fields as rows rather than columns. This can lean
> to unwieldy self-join, but at least you will not need dynamic SQL.
It's simply not true, that a flexible and dynamic system can't have a
good performance. It's just more and harder work to implement. But the
maintainance and most of all the customers (and this software is very
very succesfull) are the best reason to do this work.

This just so far, back to the technical problem. Just one question
remaining, are these so called extended procedure only procedure or
are within the c-dll also functions like we need possible ? To be more
precise, is it possible to call a XP-Funtion like 'select
MyXpFunc('Test') from customer' ?|||Ralph (Ralph.Backes@.web.de) writes:
> Misunderstanding, we don't insist on syntax, we insist on
> functionality. The implementation is of course different on every
> RDBMS. But til now every RDBMS gives us a possible way to implement
> this stuff with more or less knowledge and doing. Hard to believe
> MS-SQL can't...

Well, MS-SQL can, but...

> A stored procedure isn't useful, I need a scalar return value... This
> function is called within a reportengine and there is no other
> processhandling than straight forward doing a select.

...you are insisting on a specific syntax.

You would have to invoke a batch first calls the stored procedures,
which puts the data in a variable or a temp table (have you looked at
http://www.sommarskog.se/share_data.html yet?), and then SELECTs the
data to the client. Presuming that you are looking for many rows
at a time, this is the only way to a scalable solution.

So that batch will look different than from what it does for MySQL,
but the syntax you wished to use included dbo.yourfunc(), and I don't
think you have the dbo bit on MySQL, so you are already prepared to
handle different syntax.

> This just so far, back to the technical problem. Just one question
> remaining, are these so called extended procedure only procedure or
> are within the c-dll also functions like we need possible ? To be more
> precise, is it possible to call a XP-Funtion like 'select
> MyXpFunc('Test') from customer' ?

No, there are no extended functions, but you can call extended stored
procedures from a user-defined function. So you could write a extended
stored procedure, which returns the value in an OUTPUT parameter, and
then the scalar UDF could return that function.

But: 1) An error like an access violation in an extended stored procedure
is no small accident - the entire SQL Server perishes.
2) You will get one more - and odd - component do deploy.
3) This is definitely not going to scale.

In short: don't go there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need DTS to overwrite data

Newbie, so please bear with me.

I'm trying to automate a daily extract from our Personnel System to SQL server using DTS.

The Personnel system spits out a TXT file (containing approx 35 columns, 5000 records), which, is then picked up by DTS. I need this to overwrite the existing data, not duplicate it. However, after 1 successful load, if I try again I get a PK constraint error message.

The PK is the employee's PIN, but data in the other columns can change (e.g. Address_1) which is why I need to overwrite the data.

I have thought about importing the TXT file to a Temp Table, dropping the original table, then importing the data that way, but using DTS I cannot drop the table because of the constraints.

Any help greatly appreciated.i usually push data into a staging table, and then write the 'good records' into the fact table, and the 'bad records' get written to a exceptions table.

its pretty straight forward

you may need to fiddle with 'IDENTITY INSERT' in order to not get PK violations--

it just depends on what kindof PK you use.

i write a few views between the staging table and the fact table in order to filter out records that are already in the table, etc

it would be pretty straightforward (if you have a simple PK) in order to do this

basically i just issue a 'TRUNCATE TABLE' statement, and then i run most of my code in ActiveX script

and then when it is done, you fire a stored proc that writes the records from staging to fact.

cheers and good luck|||thanks for the info Aaron - i will give it a try.

Need assistance

Am a newbie to SQL QUERIES (reporting) - Please assist
Scenario:
I have a simple table where I have columns like
empno
empname
latedate
reasonlate
justification
This table contains mulitple data for a single empno as you can see the
structure
for eg:
empno empname latedate reasonlate justification
101 Kevin 10/1/2005 Business Training day 1
101 Kevin 10/2/2005 Business Training day 2
101 Kevin 10/3/2005 Business Training day 3
103 Tracy 10/1/2005 Personal Sick
103 Tracy 10/3/2005 Business Seminar
...
...
I need to have a report like
Kevin (101)
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy
10/1/2005 Personal Sick
10/3/2005 Business Seminar
FYI: I also have another table as EmpMaster with empno and name, so
that I can fetch those data from there.
Someone please assist me in preparing this report
Thankyou in advance
Best Regards
ShaninThis script returns data for a specified employee:
declare @.emp_name <datatype>
set @.emp_name = 'Kevin'
select latedate
,reasonlate
,justification
from dbo.EmpMaster
inner join <other_table>
on <other_table>.empno = dbo.EmpMaster.empno
where (dbo.EmpMaster.empname = @.emp_name)
You could design a procedure based on the query above.
To help you find a better solution we'd need to see DDL, more sample data
and preferably a more elaborate description of expected results.
ML|||Thank you ML for the reply
I actually need to use this script in an ASP file and generate a
report, I will not be able use hard coding, should be dynamic.
I forgot to provide the information that the core data is in a table
called HRDATA where I have those columns
empno
empname
latedate
reasonlate
justification
and have another master table viz. EmpMaster just in case we need to
join or fetch empno and empname alone
By the way what's DDL ?
Regards
Shanin|||DDL = Data Definition Language
For relevant info, please see:
http://www.aspfaq.com/etiquette.asp?id=5006
If you need to access data from a web page, then using a stored procedure is
the best way to do it. The procedure itself may not be dynamic, but the
purpose it serves is far from being "hard coded".
After you provide us with better specifications, we can help you design a
better solution.
Maybe this might also be a genuine opportunity to wipe the dust from your
copy of Books Online. :)
ML|||The specification was the one I provided above, ok let me be more
precise
I have a table viz. HRDATA andthe columns to be considered from this
are
empno - float(8)
empname - varchar(50)
loccode - varchar(50)
latedate - datetime
reasonlate-varchar(100)
justification-varchar(900)
approved-numeric(9)
now each time a employee submits his data from a ASP web form for a
certain date, a record is created in this table, hence for each date he
is late or absent he shall submit his justification, and hence the
records are like below
empno empname loccode latedate reasonlate justification
approved
101 Kevin JJ 10/1/2005 Business Training
day 1 1
101 Kevin JJ 10/2/2005 Business Training
day 2 1
101 Kevin JJ 10/3/2005 Business Training
day 3 1
103 Tracy OL 10/1/2005 Personal Sick
2
103 Tracy OL 10/3/2005 Business Seminar
1
now the HR Manager needs a report like the one below, grouped by
Empname, number and loccode
Kevin (101) - JJ
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy (103) - OL
10/1/2005 Personal Sick
10/3/2005 Business Seminar
I have another master table viz. EmpMaster where I have all the
employees name, no, and loccode. Hope am repeating what I said in my
first post.
Ths is the actual scenario, I guess it's very simple for SQL GURUS..
Regards
Shanin|||Thankx guys, I have made it possible through ASP coding itself.
Regards
Shanin|||> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
The query below will provide the needed data. However, the grouping in your
specification is really report formatting (i.e. group section headers) and
is beyond the scope of SQL. The particulars depend on your reporting tool.
SELECT
empno,
empname,
loccode,
latedate,
reasonlate,
justification
FROM HRDATA
ORDER BY
empname,
empno,
loccode
The EmpMaster table isn't needed since HRDATA contains the required info.
In fact, redundant non-key data like empname is a big red flag in a
relational database. There are some cases where redundancy is deliberately
introduced but I get the feeling that poor design is the case here. Which
name would your HR Manager expect when you have different names in both
tables for the same employee (empno)?
In the future, please include the DDL scripts and sample data like the
following. Many of your fellow SQL Server community members will take the
time to develop and test a solution to your problem. Scripts eliminate
ambiguity and is quite time consuming to construct these scripts from
narrative.
CREATE TABLE HRDATA
(
empno float(8),
empname varchar(50),
loccode varchar(50),
latedate datetime,
reasonlate varchar(100),
justification varchar(900),
approved numeric(9)
)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/1/2005', 'Business', 'Training day 1', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/2/2005', 'Business', 'Training day 2', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/3/2005', 'Business', 'Training day 3', 1)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/1/2005', 'Personal', 'Sick', 2)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/3/2005', 'Business', 'Seminar', 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Cupid Shan" <shaninraja@.gmail.com> wrote in message
news:1130751379.384631.160800@.g44g2000cwa.googlegroups.com...
> The specification was the one I provided above, ok let me be more
> precise
> I have a table viz. HRDATA andthe columns to be considered from this
> are
> empno - float(8)
> empname - varchar(50)
> loccode - varchar(50)
> latedate - datetime
> reasonlate-varchar(100)
> justification-varchar(900)
> approved-numeric(9)
> now each time a employee submits his data from a ASP web form for a
> certain date, a record is created in this table, hence for each date he
> is late or absent he shall submit his justification, and hence the
> records are like below
> empno empname loccode latedate reasonlate justification
> approved
> 101 Kevin JJ 10/1/2005 Business Training
> day 1 1
> 101 Kevin JJ 10/2/2005 Business Training
> day 2 1
> 101 Kevin JJ 10/3/2005 Business Training
> day 3 1
> 103 Tracy OL 10/1/2005 Personal Sick
> 2
> 103 Tracy OL 10/3/2005 Business Seminar
> 1
> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
> Kevin (101) - JJ
> 10/1/2005 Business Training day 1
> 10/2/2005 Business Training day 2
> 10/3/2005 Business Training day 3
> Tracy (103) - OL
> 10/1/2005 Personal Sick
> 10/3/2005 Business Seminar
> I have another master table viz. EmpMaster where I have all the
> employees name, no, and loccode. Hope am repeating what I said in my
> first post.
> Ths is the actual scenario, I guess it's very simple for SQL GURUS..
> Regards
> Shanin
>

Need an example of a trigger.

Hello,

I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin

declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn

end|||how about:

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end

or

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end

by using either of these you will delete all records when multipule records are deleted.