AdventureWorksDW database

I can not find the databases and samples for SSIS that are mentioned in the tutorial on my disks, or DVDs.

Please tell me where I may get them from?

Did you install them from SQL Server setup?

-jamie

|||

Additionally you can get the sample database and SQL Server 2005 samples from http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

Thanks,
Loonysan

|||

Loonysan,

Thanks for the link.I downloaded the samples but for creating the tutorial SSIS projects I have no luck.Are there may be bugs in this version?

Lookup Currency Key lesson:

This component has no available input columns.

|||Installing SQL Server copies the Samples off the CD, but doesn't install them.

Start > All Programs > Microsoft SQL Server 2005 > Documentation and Tutorials > Samples > Microsoft SQL Server 2005 Samples to install.

Greg.

Advantage of using a Data Source in a project

I am new to SSIS.

I came across the following article:

http://technet.microsoft.com/en-us/library/ms139848(SQL.90).aspx

It states that:

"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."

I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it’s still pointing to DS1.

I guess this is because it’s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?

Priya

AFAIK, it is used only as a template of sorts for a data source connection. Other than that, I don't really know why one would use it. I have never used them.

Maybe someone else can shed some light onto why you should use it.|||

Hi Priya,

I went through the article and what i understood is:

"A datasource is created outside the reference of a package" , means you can share it across many packages by pointing your connection manger to the datasource you just created.Consider this scenario where you have 10 packages pointing to same source then you may use a Datasource so that all connection managers point ot same datasource.Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently.

Note: Please correct me if I am wrong.

|||

When I started with SSIS , I also wanted to use Data source, since it seemd reasonable to reuse the Data source in various packages. Until you want to use package configuration files to store your connectionstrings. Then it is no longer possible to use datasources. From now on I do not use Data sources any longer. I am using package configuration files that contain the connctions strings of my connections, and in my packages I use the appropriate configuration file. I also use environment variables to proint to my package configuration files. This allows for a somewhat easier deployment of packages on developer and production servers.

|||This is the correct way of handling environment differences with SSIS.
As said, data sources are for design time.

A nice little "feature" of data sources: Delete it and it deletes all your connections which are derived from it.|||Hi all,

Thanks for ur replies.
I'v used configuration file to configure connection manager before and you can't configure data source with it.

Unni, as you'v said that:
"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

Did you try out updating the datasource to point to a different source? Does it get reflected in the connection managers? As i've mentioned in the 1st post the connection manager was still pointing to the older db in my case.
Priya

|||

Let me clarify why I am using configuration files and not datas sources:

- datasources are fine if you always edit your package with visual studio as a developer, no matter where your packages are deployed.

- I have to design a package on a development server where I have full access. but my package will be deployed in a production environment where I will have very little access. The sysadmin will now how to make the enviroment variable en how to edit the configuration file with notepad, those are all the tools he will need. The ssis packages will be restored on the production server via backup restore.

So as far I am concerned this way of working is SOX compliant, and therefore I do not use datasources.

|||In general, I think the consensus among most of the regular posters on the forums is that using data sources is more trouble than it is worth. If updating a data source actually updated all the connection managers derived from it, there might be a benefit to using them. But each connection manager maintains its own copy of the connection string, so there is not much value added by using the data source. Plus, the messages to Synchronize Connection Strings are really annoying |||

Hi,

"Hence at later point of time if you want to change the source you just need to update you datasource rather than updating each connection manager independently."

I have not tried this, I am using configuration files in my package.

|||Yes, if you open each and every package in the project, it will prompt you to change the connect string. It doesn't automatically propagate the change. Configurations are a much easier way of getting the same result.

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO.Net 2.0 Bulk copy vs SSIS

For simply loading a big amount of data. Is the the performance similar?"nick" <nick@.discussions.microsoft.com> wrote in message
news:B546BA27-E6A2-4477-926E-A37E3F6A635D@.microsoft.com...
> For simply loading a big amount of data. Is the the performance similar?
Yes, the performance is similar. You should choose between them based on
other factors.
David

AdjustTokenPrivileges () failed

I have an SSIS package that parses a text file into 3 smaller text files and then takes the data and puts it into tables. The package runs fine up to the point where it needs to insert the data. I turned logging on but no errors are generated. But I do get a file named SQLDUMPER_ERRORLOG.log that is generated with the info below. Any ideas of where to look?

11/16/06 13:31:38, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump

completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp

11/16/06 13:31:43, ACTION, DtsDebugHost.exe, Watson Invoke: No

11/16/06 13:31:43, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:44, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpWriteDump

() Failed 0x80070005 - Access is denied.

11/16/06 13:31:44, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Watson Invoke: No

Do you have anything in your Windows Event log that might indicate a better error?

Also, how are you authenticating to the databases? SQL Server users? Active Directory?

I'm just blurting out stuff to check, I guess.

Phil|||Also, you didn't need to start this thread when you replied to the other one. We don't need two threads about the same topic. Having more than one thread only makes it cumbersome to help you.|||

Nothing in the Application Log. I've tried authentication using windows auth and sql auth both result in the same error.

I was using an OLE DB Destination and I changed it to a SQL Server Destination which got rid of the error above but presented another. I still believe OLE DB Destination should work though.

I should add that I can run the package from my machine without a problem. But whenever I try to run it from the server It will be scheduled on is when I have the problem