How do I make a function private?

Hi,
Am just wondering how to make a function private within a package??
FUNCTION get_new_student_id
Thanks.Simply leave the function out of the package specification:

CREATE OR REPLACE PACKAGE pkg IS
PROCEDURE public_proc;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg IS

-- This procedure is private: it can only be called from within the package
PROCEDURE private_proc IS
BEGIN
NULL;
END;

PROCEDURE public_proc IS
BEGIN
private_proc;
END;

END;
/|||In case you're interested... you can also do this within a procedure.

CREATE OR REPLACE PROCEDURE INET.show_test
IS

FUNCTION my_test (a VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN a;
END;

BEGIN

dbms_output.put_line( my_test('b') );

END;

JoeB

How do I know why some records were not uploaded

This is an upload from foxpro to SQL Server.

My package loads about a million records and reject about 5,000 records. The rejected records are routed to a foxpro table.

Is there a way to find out why the records were rejected and sent to the error table?. I need to determine the reason so I can fix the records and try to upload them again.

Any help is appreciated.

Thanks

Try removing the error flow on the destination (sql server) and see what the error message is.|||Turn on verbose logging, and make sure that your error code and error column from your sql destination are part of your error destination.|||

Thanks, for the help. I do I turn on verbose logging?

|||

SQL Server 2005 Books Online

Adding and Configuring Logging

|||

I think you grabbed the wrong url (at least it didn't link up right when I tried to click it)...

Here is the link I saw for adding and configuring logging:

http://technet.microsoft.com/en-us/library/ms167456.aspx

Here is a more in depth list of topics on logging:

http://technet.microsoft.com/en-us/library/ms141727.aspx

|||Yes, that is the one.... thanks Eric

How do I identify a command in a job step?

Hi
I'm having a job that runs a number of steps. One of them runs the CmdExec
"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
how do I find out which one it is? I've looked a bit around to see if I can
find a table where I can find the number and then maybe see the name of the
job, but with no luck.
Can any of you help with this?
Regards
SteenWhen the DTSRun command uses /~Z, it means the command to
run the DTS package is encrypted. Run a trace or Profiler
when the job runs. This will allow you to get the package
name or package guid.
-Sue
On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Hi
>I'm having a job that runs a number of steps. One of them runs the CmdExec
>"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
>how do I find out which one it is? I've looked a bit around to see if I can
>find a table where I can find the number and then maybe see the name of the
>job, but with no luck.
>Can any of you help with this?
>
>Regards
>Steen
>|||Ok...found some more info about it in BOL (...just have to look for the
right thing..:-)..). The /Z argument apparently tells that the command line
is encrypted. That's fine, but...I still need to find out which command it's
running. Any change of doing that?
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:O5Z2ZETVEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm having a job that runs a number of steps. One of them runs the CmdExec
> "DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> how do I find out which one it is? I've looked a bit around to see if I
can
> find a table where I can find the number and then maybe see the name of
the
> job, but with no luck.
> Can any of you help with this?
>
> Regards
> Steen
>|||Thanks Sue
I've just tried to run a trace, but I must admit that my knowledge in
running traces might not be good enough, since I can't find any references
in there that indicates which package is being run.
Are there any easy or "obvious" data to look for to find out which package
is being executed?
Regards
Steen
"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
> When the DTSRun command uses /~Z, it means the command to
> run the DTS package is encrypted. Run a trace or Profiler
> when the job runs. This will allow you to get the package
> name or package guid.
> -Sue
> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
> <SPE@.REMOVEdatea.dk> wrote:
> >Hi
> >
> >I'm having a job that runs a number of steps. One of them runs the
CmdExec
> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> >how do I find out which one it is? I've looked a bit around to see if I
can
> >find a table where I can find the number and then maybe see the name of
the
> >job, but with no luck.
> >
> >Can any of you help with this?
> >
> >
> >Regards
> >Steen
> >
>|||Steen,
Capturing SQL:StmtCompleted or SP:Completed will give you
TextData values along the lines of:
exec msdb..sp_get_dtspackage N'YourPackage', null, null
-Sue
On Fri, 18 Jun 2004 15:26:26 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Thanks Sue
>I've just tried to run a trace, but I must admit that my knowledge in
>running traces might not be good enough, since I can't find any references
>in there that indicates which package is being run.
>Are there any easy or "obvious" data to look for to find out which package
>is being executed?
>Regards
>Steen
>"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
>news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
>> When the DTSRun command uses /~Z, it means the command to
>> run the DTS package is encrypted. Run a trace or Profiler
>> when the job runs. This will allow you to get the package
>> name or package guid.
>> -Sue
>> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
>> <SPE@.REMOVEdatea.dk> wrote:
>> >Hi
>> >
>> >I'm having a job that runs a number of steps. One of them runs the
>CmdExec
>> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
>but
>> >how do I find out which one it is? I've looked a bit around to see if I
>can
>> >find a table where I can find the number and then maybe see the name of
>the
>> >job, but with no luck.
>> >
>> >Can any of you help with this?
>> >
>> >
>> >Regards
>> >Steen
>> >
>

How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I figure out where SSIS logs are?

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

Open them in a text editor. I'm a bag fan of Textpad (www.textpad.com)

You control where they are. You tell tell the package where to log to.

-Jamie

|||

Hi Jamie,

Come on man, textpad? Is my favourite without a doubt, I like it a lot too.

I was saying, in what path ssis leave its logs. where?

Thanks again

|||

Like I said previous, the logs are put wherever you tell SSIS to put them. That can be a text file (it is you, not SSIS, that defines where that file resides), SQL Server (in which case they'll be in the msdn..sysdtspackageslog90 table if memory serves), event log, SQL Profiler or an XML file.

-Jamie

How do I export a DTS from one server and import into another?

Good day all!

Hopefully this is just a quickie but we'll see, won't we? :p

I have a DTS package that needs moving from one server to our test bed, which is completely disconnected from the network - therefore I have to transport it by removable media (USB flash drive for instance).

Is there anyway I can do this? I've tried doing a "save as..." Structured Storage File (*.dts) but then I have no clue as to what to do with it to get it back in to the database!

Any suggestions me hearties?
Yarr!Yeah It is a quite easy if you are using same version of SQL on two boxes...
From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
Copy this file on the disconnected box and Import.

Hope this will help you.|||Dead easy this one. Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste ;)

I've never had much luck moving DTS packages around (portability is another nail in their coffin IMHO). Have you tried googling? You will find lots of articles about this. Also check out http://www.sqldts.com/ - there ain't much to do with DTS that ain't on there.|||From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
Copy this file on the disconnected box and Import.

It appears to be trying to export the tables as well? I think I might just be missing something

Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste

Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQL

Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.

Oh and another justification for DTS in this case - it needed to be done quick. It's for the purpose of the first bulk load of data from another source a number of times (test, test and test again!).

Any other ideas peoples?|||Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.Really? Article linked from the home page:
http://www.sqldts.com/204.aspx|||Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQLBulk insert - wrapper for BCP. Check out in BoL. Gets the stuff in:


BULK INSERT my_table FROM 'C:\stuff.csv'
WITH ( codepage = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 1, BATCHSIZE=1000, MAXERRORS=100000)


BCP for knocking it out:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM my_table" queryout "C:\stuff.csv" -c -T -t","'|||in sql 2K all you have to do is open up the package in the designer and do a save as structured storage file. Then on your other server all you have to do is open that file and save it to that sql server.

you will have to change the properties of your connection objects and anything else that points to things on the network. it's easy.|||The simplest method is to use Enterprise Manager to open an existing package, and then select Save As from the Package menu. You can then save the package directly to another server, or change the storage location to save it to a structured storage file if you do not have direct access to the final destination. The storage file can then be manually copied to the destination server. At the destination, open Enterprise Manager and right-click the Data Transformation Services node to access the Open Package option. This allows you to select a structured storage file package to open. You can then use the Save As option to save it to the new server.

I don't quite know how I missed that bit! :(

Poots, what does; "-c -T -t" mean/do?
I'm going to look into making it into a SP after we have the thing running anyhow. For now we needed the quick solution so that's what I provided.

Thanks guys!
I'll be back to bug you when I'm scripting this out soon ;)|||Poots, what does; "-c -T -t" mean/do?Would you like me to type BCP into the BoL search for you? ;)|||I'm getting lost in acronyms... BCP = Best Common Practice?
Nope, apparently it's Bulk Copy Program...

If I get time later I'll go look it up properly (gotta hit the test bed now if I still want to go on holiday next week ;))|||BCP is a very fast method of moving data about. Remember the "remove identity property from column" thread? One suggested solution was BCP the data out, change the table, BCP back in.

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?

How do I edit an Imported DTS package in SSIS?

I have used the wizard to import a DTS package from a SQL 2000 server to our new SQL 2005 server and need to make edits to reference the new server and database. I am able to see the package within the SQL Management studio under Integration Services, but I cannot find it in the Development studio?

What am I missing?

Thanks

The development studio is an offline tool that works on file packages only. Export the package from SQL Server / IS and add it to a project in BIDS.