Data Validation

Being relatively new to SSIS, I'm looking for advice, or a best practice, regarding data validation before extracting the data for a transformation.

One of my project's require that certain data be validated in staging tables before it is loaded. The validations include checking for null values, verifying that a field is populated with apropriate values etc... The entire batch of data (good records and bad records) may be rejected depending on the validations.

I have a couple of different thoughts on how this could be handled...

Run a series of validation queries on the data before executing an SSIS package Run some kind of validation transformation (does one exist or should I write a custom transformation?) Place contraints on the target tables so that bad records error out on the load Something else... I could be missing the completely obvious

#3 doesn't seem to viable as the entire load may be rejected if some of the data is bad...

Any thoughts?

You could filter out the bad data very easily using a Conditional Split transform. Better still, you can pipe that bad data somewhere for later analysis.

Does that help?

-Jamie

|||I like both of thoses suggestions. With the conditional split transform, could I then update the original record with a status flag?|||

Once you pipe the data off elsewhere from the Conditional Split you can do what you want with it so yes, you should be able to do this! You would use an OLE DB Command transform.

You may have a problem (i.e. blocking) when trying to update a table that you are selecting from in the source - but this can be easily alleviated by dropping the update dataset into a raw file and then doing the update in a seperate data-flow.

-Jamie

|||

Thank you!

I was just testing an update using the OLE DB Command Transform. The raw file seems like good work around if the row is locked!

Data Type in Oracle is not translating in SSIS datatype

My OLDB data source in the Lookup Transformation is not converting

When I try to join the colunms it claims the data types do not match the only differnece is the percision.?

ORACLE
select CAST(group_id AS NUMBER(18)) group_id from blah blah table
SSIS data type = [DT_NUMERIC], Percision = 38

SQL Sever data source
SELECT
CAST(PRINCIPAL_ID as numeric) GROUP_ID,
SSIS data type = [DT_NUMERIC], Percision = 18The metadata of the columns being joined should have to match exactly. (same precision, same

scale)

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data type conversions

What are the pros/cons of datatype conversions using the advanced properties of the source adapters or transformation components themselves vs. using the data conversion transformation? Sometimes I am able to do all the conversions I need using the advanced properties rather than use a data conversion component, but didn't know if this was considered bad practice, or has some other impact that isn't immediately apparent.

Thanks

Kory

I offten use Derived Column component as data conversion

I think that is well used to convert lots of data type at the same time

|||

I'd say doing as much conversion up front in a source as possible would be preferable. Doing it later on means you have a wider buffer, to accomodate the two columns required. Saying that, they can be harder to maintain sometimes, as refreshing the metadata can loose your little tweaks. Depends on how stable you view your source perhaps.

Using one transformation over another, Derived Column or Data Conversion would not seem like much of a choice really, I doubt there is much to choose between. They are both synchronous transforms, so will require two "columns" in the buffer, the current type and the new type. That obviously makes for a wider buffer and therefore more cost, due to less rows per buffer.

Still do what works for you, but test a few scenarios if you can and see which works best for you. If performance or maintanece a bigger cost to you?

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data Transformation: copying a table to a table... How does it work?

Hello,

Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.

In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?

What type of cursors and where? Not really sure where that comes from, as this is not really ADO or T-SQL which is were I have com eaccross cursors before.

SSIS and DTS shouldn't use cursors for extract or loading SQL Server, just look at the connections used, standard OLE-DB stuff. All stock sources and destinations are geared towards standard connections, and they use the common providers.

The buffer is a SSIS engine feature, so not cursors either.

If you do not have permissions that allows SSIS or DTS to run then you will not be able to simulate it either, as permission requirements will be the same. If you extract data you need read permissions to run your own code or a SSIS package that does the same thing for example.

Data Transformation.

Hello,
I want to do some data transformation from one Database to another.
My source DB is a MS SQLServer to which I can connect using OLEDB/SQLnative.
My destination DB is a different type of database to which I can connect
using Connection Manager. But for data transformation I need a destination
connection. In the data pump area I don't have the option to choose an ODBC
connection type. How can I connect to my destination database if there is no
ODBC connection type to choose from in the Dataflow Tab?
Help.
Regards,
Sami
[Remove Numbers from e-mail address to use it]I am using SQLBase from Gupta. The only way to connect to the Database is
over ODBC. But as I mentioned, if I choose to do a Data Transformation I
don't have the option of choosing ODBC as my destination connection.
Regards,
Sami
[Remove Numbers from e-mail address to use it]
"Allan Mitchell" <allan@.no-spam.sqldts.com> schrieb im Newsbeitrag
news:f0343b032956e8c851df501af0f0@.msnews
.microsoft.com...
> Hello Sami,
>
> And what database type is your destination?
> You may find this post helpful
> http://forums.microsoft.com/MSDN/Sh...=70469&SiteID=1
>
> Allan Mitchell
> www.SQLDTS.com
> www.SQLIS.com
> www.Konesans.com
>
>|||Sami,
Create a .Net Providers/ODBC data providers connection and configure it to
use a system DSN that connects to your SQLBase. Within the dataflow, create
a DataReader Destination and configure it to point the data connection that
you created. On the second tab, you will have to supply the SQLCommand to
write the data.
Hope this helps.
-Steven
"Sami" wrote:

> I am using SQLBase from Gupta. The only way to connect to the Database is
> over ODBC. But as I mentioned, if I choose to do a Data Transformation I
> don't have the option of choosing ODBC as my destination connection.
> --
> Regards,
> Sami
> [Remove Numbers from e-mail address to use it]
> "Allan Mitchell" <allan@.no-spam.sqldts.com> schrieb im Newsbeitrag
> news:f0343b032956e8c851df501af0f0@.msnews
.microsoft.com...
>|||Thank you very much for all your help.
Regards,
Sami
[Remove Numbers from e-mail address to use it]
"Sami" <s8a2m9i1_i5s1l9a6m@.hotmail.com> schrieb im Newsbeitrag
news:OPXbc4ygGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I want to do some data transformation from one Database to another.
> My source DB is a MS SQLServer to which I can connect using
OLEDB/SQLnative.
> My destination DB is a different type of database to which I can connect
> using Connection Manager. But for data transformation I need a destination
> connection. In the data pump area I don't have the option to choose an
ODBC
> connection type. How can I connect to my destination database if there is
no
> ODBC connection type to choose from in the Dataflow Tab?
> Help.
> --
> Regards,
> Sami
> [Remove Numbers from e-mail address to use it]
>|||Thank you very much for all your help.
Regards,
Sami
[Remove Numbers from e-mail address to use it]
"Sami" <s8a2m9i1_i5s1l9a6m@.hotmail.com> schrieb im Newsbeitrag
news:OPXbc4ygGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I want to do some data transformation from one Database to another.
> My source DB is a MS SQLServer to which I can connect using
OLEDB/SQLnative.
> My destination DB is a different type of database to which I can connect
> using Connection Manager. But for data transformation I need a destination
> connection. In the data pump area I don't have the option to choose an
ODBC
> connection type. How can I connect to my destination database if there is
no
> ODBC connection type to choose from in the Dataflow Tab?
> Help.
> --
> Regards,
> Sami
> [Remove Numbers from e-mail address to use it]
>

Data Transformation with ActiveX

Hello,

I am importing data from a textfile of 1,5 GB to my Sql-server and I use activeX for the transformation. I need ActiveX script because I have to change some formats etc.
But, I think it is very slow and I want it to speed up.
Do you have some hints to speed up the transformation?
Thx for the advice!

TomInstead of having multiple Activex scripts , make it single script

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace

Data Transformation Services Revisited

I am trying to export a table from SQL Server to Oracle8i home edition. I want to schedule the export package to run at a certain time. The package gets created, but when the time is reached for the package to execute, it doesn't execute. There are no errors at all. It just doesn't run. The package is there because I see it in the Data Transformation Services->Local Packages list. Any ideas? Thanks.If you scheduled the package to run you should see it Enterprise Manager under the server in Management/SQL Server Agent/Jobs.
There you will see last run status among other things.

Of course the SQL Server Agent needs to be running.|||Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks|||Originally posted by exdter
Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks

Do a refresh on the jobs. If it isn't there, then that is your problem. After you right-click the DTS package and do schedule, you should see the job in the "Jobs" panel.|||I don't think you would get an error that it is not running

In Enterprise Manage you see if it is running by the small green "play" icon on it. You can start it from there with right clicking on it.
Or you can start it with Service Manager.

It would probably be a good thing to set it to always start automatically.|||The jobs were there, all with error messages. The Server Agent is running as well.|||Then you know the schedule works at least :-)

Can you run the task manually, and does it work without errors then?

Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.|||Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.

OK, that's a different error than what you were seeing before (no jobs).

Try double-clicking the job, click the "steps" tab, select the only step, click edit, cut and paste the command to a command prompt and see what you get.

That will probably fail and give you a more detailed error. Otherwise, you probably have a permissions issue.|||Manually the job works. Thanks for your help.|||When I put the line in a command prompt, everything worked. The table was created in Oracle.|||Originally posted by exdter
When I put the line in a command prompt, everything worked. The table was created in Oracle.

If the job reliably fails when automated and reliably works when executed manually, it is almost definitely a permissions issue. Right-click the job, do "Start Job", wait a few minutes, do a refresh, and check if the job does reliably fail when scheduled.

Check who owns that job. Try changing that to sa or local Admin. You also may want to try deleting and recreating the job (I've seen that help).|||I created the job logged in as administrator. I deleted and recreated the job more than once, and it still doesn't run on a schedule. Thanks for your help.|||The scheduled job will run under the credentials the SQL Server Agent has if using Windows integrated security.
For the connection with Oracle I guess you set that in the Oracle connection in the DTS package. (I don't know much about Oracle security).|||I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.|||Originally posted by exdter
I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.

This is an almost sure sign of a permissions issue. Did you look at the Windows Event Log? The error message for scheduled jobs should be in there.|||I couldn't find anything in there about it.|||Just to make sure: Have you installed (Oracle) SQL*Net on the SQL Server? SQL*Net is similar to Client Connectivity in SQL Server. It installs all the underlying DLL's, executables, and such that any application (DTS included) would need to connect to an Oracle Database.

If so, can you do a TNSPing to the SID that you are trying to reach? DTS is horribly client bound, so running the package manually would mean that you are running the package from your lap/desk-top.|||I didn't have the SQL* Net installed. Thanks.

Data Transformation Services Migration wizard

i am trying to use the DTS migration wizard in sql server 2005 to migrate some of the DTS packages that i have on sql server 2000.

After entering the source and destination server i get the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Does anyone know the reson behind this?

Thanks for any help.

I've just installed Server 2005 and am getting the same message. The earlier threads refer to special characters and leading or trailing spaces. I've tried the wizard on a few packages that have nothing but letters in the name, and I get the above message. I tried repairing .NET 2.0 as well (didn't work).

What else should I/we try?

Thanks,

K

|||

Found a forum where a user clarified that NONE of the DTS packages can have a leading/trailing space. Well, one out of a hundred or so packages had a space; after I fixed that one, the wizard worked.

Find the spaces (thanks to Joseph Sack's SQL Server blog):

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

and I'd suggest: or name LIKE ' %'

|||

Thanks a lot.

I had one package that had a space. After i deleted the space i was able to get a little further. but when i hit finish, All the packages display "Stopped" and none gets transferred.

Thanks

Data transformation services migration problem

I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
I want to do some migrations and tried to migrate Data Transformations.
In Legacy I choose " Data Transformation Services" and rightclick on
"migration wizard"
Everything looks fine and the wizard is telling me verything was ok.
But now I cannot find the new migrated files?
Where can I find these?
Hans
The tables have been copied, no problem, but the Data Transformations does
not work. I also cannot create new data transformations like I did in
SQL2000
Hans
"John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
news:eTMLA1$nIHA.4848@.TK2MSFTNGP05.phx.gbl...
> "Hans" <info@.tct.nl> wrote in message
> news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl ...
> Hi
> Have you tried backing up the databases and restoring them on the new
> server? Check out http://support.microsoft.com/kb/314546
> John
>

Data transformation services migration problem

I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
I want to do some migrations and tried to migrate Data Transformations.
In Legacy I choose " Data Transformation Services" and rightclick on
"migration wizard"
Everything looks fine and the wizard is telling me verything was ok.
But now I cannot find the new migrated files'
Where can I find these?
Hans"Hans" <info@.tct.nl> wrote in message
news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl...
>I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
> I want to do some migrations and tried to migrate Data Transformations.
> In Legacy I choose " Data Transformation Services" and rightclick on
> "migration wizard"
> Everything looks fine and the wizard is telling me verything was ok.
> But now I cannot find the new migrated files'
> Where can I find these?
> Hans
>
Hi
Have you tried backing up the databases and restoring them on the new
server? Check out http://support.microsoft.com/kb/314546
John|||The tables have been copied, no problem, but the Data Transformations does
not work. I also cannot create new data transformations like I did in
SQL2000
Hans
"John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
news:eTMLA1$nIHA.4848@.TK2MSFTNGP05.phx.gbl...
> "Hans" <info@.tct.nl> wrote in message
> news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl...
>>I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
>> I want to do some migrations and tried to migrate Data Transformations.
>> In Legacy I choose " Data Transformation Services" and rightclick on
>> "migration wizard"
>> Everything looks fine and the wizard is telling me verything was ok.
>> But now I cannot find the new migrated files'
>> Where can I find these?
>> Hans
> Hi
> Have you tried backing up the databases and restoring them on the new
> server? Check out http://support.microsoft.com/kb/314546
> John
>|||"Hans" <info@.tct.nl> wrote in message
news:48083b38$0$14782$ba620dc5@.text.nova.planet.nl...
> The tables have been copied, no problem, but the Data Transformations does
> not work. I also cannot create new data transformations like I did in
> SQL2000
> Hans
> "John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
> news:eTMLA1$nIHA.4848@.TK2MSFTNGP05.phx.gbl...
>> "Hans" <info@.tct.nl> wrote in message
>> news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl...
>>I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
>> I want to do some migrations and tried to migrate Data Transformations.
>> In Legacy I choose " Data Transformation Services" and rightclick on
>> "migration wizard"
>> Everything looks fine and the wizard is telling me verything was ok.
>> But now I cannot find the new migrated files'
>> Where can I find these?
>> Hans
>> Hi
>> Have you tried backing up the databases and restoring them on the new
>> server? Check out http://support.microsoft.com/kb/314546
>> John
Hi
In SQL 2005 (which comes with SBS 2003 R2) you should use SSIS rather than
DTS. These may help answer some of your questions
http://msdn2.microsoft.com/en-us/library/ms345120.aspx
http://msdn2.microsoft.com/en-us/library/ms143706.aspx
John|||Where can I find SSIS program?
Or do you mean that VB .NET ? But that's very complicated for a simple user.
What I still don't understand is:
The migration wizard imports the DTS into the SQL 2005, but I cannot find it
anywhere in the databases? Where is this saved and where can I open the
imported files?
Hans
"John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
news:%23%23Qke8SoIHA.420@.TK2MSFTNGP02.phx.gbl...
> "Hans" <info@.tct.nl> wrote in message
> news:48083b38$0$14782$ba620dc5@.text.nova.planet.nl...
>> The tables have been copied, no problem, but the Data Transformations
>> does not work. I also cannot create new data transformations like I did
>> in SQL2000
>> Hans
>> "John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
>> news:eTMLA1$nIHA.4848@.TK2MSFTNGP05.phx.gbl...
>> "Hans" <info@.tct.nl> wrote in message
>> news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl...
>>I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
>> I want to do some migrations and tried to migrate Data Transformations.
>> In Legacy I choose " Data Transformation Services" and rightclick on
>> "migration wizard"
>> Everything looks fine and the wizard is telling me verything was ok.
>> But now I cannot find the new migrated files'
>> Where can I find these?
>> Hans
>> Hi
>> Have you tried backing up the databases and restoring them on the new
>> server? Check out http://support.microsoft.com/kb/314546
>> John
> Hi
> In SQL 2005 (which comes with SBS 2003 R2) you should use SSIS rather
> than DTS. These may help answer some of your questions
> http://msdn2.microsoft.com/en-us/library/ms345120.aspx
> http://msdn2.microsoft.com/en-us/library/ms143706.aspx
> John
>|||"Hans" wrote:
> Where can I find SSIS program?
> Or do you mean that VB .NET ? But that's very complicated for a simple user.
> What I still don't understand is:
> The migration wizard imports the DTS into the SQL 2005, but I cannot find it
> anywhere in the databases? Where is this saved and where can I open the
> imported files?
> Hans
>
> "John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
> news:%23%23Qke8SoIHA.420@.TK2MSFTNGP02.phx.gbl...
> >
> > "Hans" <info@.tct.nl> wrote in message
> > news:48083b38$0$14782$ba620dc5@.text.nova.planet.nl...
> >> The tables have been copied, no problem, but the Data Transformations
> >> does not work. I also cannot create new data transformations like I did
> >> in SQL2000
> >>
> >> Hans
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
> >> news:eTMLA1$nIHA.4848@.TK2MSFTNGP05.phx.gbl...
> >>
> >> "Hans" <info@.tct.nl> wrote in message
> >> news:48061e52$0$25482$ba620dc5@.text.nova.planet.nl...
> >>I have now SBS 2003 with SQL. I had a SBS2000 with SQL.
> >> I want to do some migrations and tried to migrate Data Transformations.
> >>
> >> In Legacy I choose " Data Transformation Services" and rightclick on
> >> "migration wizard"
> >> Everything looks fine and the wizard is telling me verything was ok.
> >>
> >> But now I cannot find the new migrated files'
> >> Where can I find these?
> >>
> >> Hans
> >>
> >> Hi
> >>
> >> Have you tried backing up the databases and restoring them on the new
> >> server? Check out http://support.microsoft.com/kb/314546
> >>
> >> John
> >>
> > Hi
> >
> > In SQL 2005 (which comes with SBS 2003 R2) you should use SSIS rather
> > than DTS. These may help answer some of your questions
> > http://msdn2.microsoft.com/en-us/library/ms345120.aspx
> > http://msdn2.microsoft.com/en-us/library/ms143706.aspx
> >
> > John
> >
Hi
Migrated DTS packages will be under the Management\Legacy\Data
Transformation Services See
http://technet.microsoft.com/en-us/library/ms143706.aspx
You should aim to re-write the packages in SSIS (integration services) to
take advantage of the new system. To write a new package you will have to
use BIDS (SQL Server Business Intelligence Development Studio) and create an
Integration Services Project. More information about this can be found in
Books Online also check out http://www.sqlis.com/ and the videos on
http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
John

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"

Data Transformation Services

How can I make a DTS in MSSQL Server Management Studio Express?

I know how to do that in SQL Server Enterprise Manager.

Is this restricted in the express?

Thanks.

SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"

Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?

Data transformation services

How to start "Data transformation services" using Remoting (over the net)?

What are you actually trying to do?

DTS is just a set of COM objects, tehre is nothing to start, there are objects which need CoCreated.

Data Transformation Services

How can I make a DTS in MSSQL Server Management Studio Express?

I know how to do that in SQL Server Enterprise Manager.

Is this restricted in the express?

Thanks.

SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"

Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?

Data Transformation Services

When I use the DTS to export data from SQL Server to Oracle 8i, I have no trouble doing it by exporting a whole table. However, if I try to export to Oracle using an SQL query, the table in Oracle gets created, but the table doesn't get populated by the data in my query. I get an ok on the creation of the table, but on transferring of data, I get an error that the table in Oracle doesn't exist. Any ideas? Thanks.Is the creation of the table and pumping of data separate tasks?
If so can you execute them one at a time in the DTS package designer?

I don't know if there is a good way of debugging DTS packages, if anyone knows of it I would appreciate to know how too.

Data transformation services

Hi

I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.

I have tried to install DTS with no success. I am running the following

Visual web studio express

SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components

Installed SQLEXPR_Toolkit.exe with all its options

Installed SQLServer2005_DTS.MSI

When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.

Can somebody please help me.

DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.

|||

Hi

Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.

Regards