Greetings my SSIS friends
This is probably a silly question but what exactly is the purpose of creating Data Sources when it is possible to set up individual connection managers?
Thanks for your help in advance.
If you want to reuse connection managers across different packages, you can create data sources and choose new or existing data connections.I know that you can do this, because I have read about it in MSDN, but I'm not suer about the syntax.
I am creating a report that needs an SSIS type data source. I have deleted the comments in the relevant config files both on my local machine (development) and the reporting services server, and can see SSIS as data type. Now for the tricky bit!
I go to create a new data source. Give it a name, choose SSIS as a data type, and now for the connection string. I "think" I know what I would do if it were a file system SSIS package, but it is stored in the MSDB. What connection string do I use to connect to this data source? There are no DTSX files on the server. Somehow, I presume, I have to talk directly to the MSDB..... Any ideas anyone?
OK, so I gave up on trying to get data out of the MSDB! I capitulated to the MSDN way of doing things and saved my SSIS package as a file system object and attached in that way.
Works like a dream - surprisingly fast. I wonder how it will perform (if at all) when using report snapshots!
If anyone has been successful in reporting over an SSIS, MSDB data source, I would appreciate it if you could post the solution here.
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...
Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.
|||Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
MikeyNero wrote:
Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
|||
Hello,
I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)
I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
Cheer
Eric
|||MikeyNero wrote: Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"
In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.
Just my two-penneth worth.
-Jamie
|||
Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.
My advice? If you're using SSIS, don't use DSVs!
-Jamie
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...
Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.
|||Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
MikeyNero wrote:
Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
|||
Hello,
I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)
I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
Cheer
Eric
|||MikeyNero wrote: Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"
In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.
Just my two-penneth worth.
-Jamie
|||
Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.
My advice? If you're using SSIS, don't use DSVs!
-Jamie
The OLE driver also seems to have trouble running queries. What I mean by that is that it only works if I use the Table or View option on the OLE data source, it hiccups if I try to use the SQL Command option.
Sorry if I'm overloading the forum, but we have a number of questions.One thing you can try is to create an ADO.NET:ODBC connection in SSIS and use DataReaderSource adapter to pull out data, see whether that works.
We got reports of similar issues when customers use IBM OLEDB provider and the root causes seemed to fall into the provider's plate, not actually SSIS problems.
If instead you are using MS OLEDB Provider for DB2 and facing issues, please provide more detailed information about, for example the failure info, how "slow" when processing large amount of rows etc, and we will be able to better assist you.
Thanks
Wenyang|||Yes, I figured out the ado.net/datareader solution this morning and it works pretty well. We are using IBM's OLEDB provider. I will try and get the MS OLEDB for DB2 to see how that does.
Thanks for the help!
Harrysql
Currently, I am working SSIS project that its data source from ORACLE 9i. I installed full SSIS in server and also ORACLE. So, I have three connections available in server. They are the following below.
Oracle Client
Microsoft OLE DB Provider for Oracle
Oracle Provider for OLE DB
The problem is the three component can not recognize parameterization and ORACLE store procedures.
Finally, I executed the store procedures by ORACLE package from command prompt (bat files). So, the command execute from SSIS package.
Ashari Imamddin
You can put your SQL statements into SSIS variables and then via expressions pass them the parameters you need. Then in you OLE DB source choose SQL Command from variable.Hi -- I am fairly new to SSIS development, although I am starting to appreciate it more an more, especially since I have started getting into extending the object model. Here's my question:
I have a data flow that pulls data from any number of different delimited files with different numbers of columns. I have had no problem dealing with setting up run-time file locations and file names by using the expressions of a flat file data source, and i have been able to pretty easily deal with varying file delimiters by standardizing the files before they get into the data flow. however, I have not been able to come up with a solution that will allow my data source to discover its column info at run time, and then pass that information on to the data flow task. all i really care about is being able to properly parse the individual rows into individual column data by the flat file data source because the data flow itself is able to discover the actual data that the columns hold at run-time.
i would very much appreciate any feedback from anyone on possible solutions for this.
thanks!
i have seen a suggestion below from a similar problem that someone had that suggests loading all of the data as a single column and then parsing it out in the data flow. not a bad idea.
has anyone else implemented any other solutions for this?
I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.
I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?
What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....
Thanks for your response
In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.
In my opinion - don't use them.
-Jamie
|||
Hi all, What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?
That's correct.
I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.
|||I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.
Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.
|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.
Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.
Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.
|||Of course I use configurations ...But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||
Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.
If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.
The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.
Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.
Do you see any other benefits of data sources over configurations for connection management?
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
ne comments please...
I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.
I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?
What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....
Thanks for your response
In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.
In my opinion - don't use them.
-Jamie
|||
Hi all, What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?
That's correct.
I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.
|||I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.
Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.
|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.
Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.
Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.
|||Of course I use configurations ...But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||
Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.
If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.
The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.
Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.
Do you see any other benefits of data sources over configurations for connection management?
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
ne comments please...
I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.
I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?
What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....
Thanks for your response
In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.
In my opinion - don't use them.
-Jamie
|||
Hi all, What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?
That's correct.
I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.
|||I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.
Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.
|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.
Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.
Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.
|||Of course I use configurations ...But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||
Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.
If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.
The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.
Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.
Do you see any other benefits of data sources over configurations for connection management?
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
|||I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)
can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?
ne comments?
regards,
Anas
ne comments please...
hi all,
i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:
how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:
select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.
please help,
tomer
You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.
-Jamie
|||
hi jamie,
thank you for your advice but i still did not understand completely.
the example u sent me is with ole db data source. lets say i have created the variable
and put there the select. now how do i config the data flow to use it?
can you give me another example?
thx,
Tomer
|||Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.
-Jamie
|||
hi again Jamie,
i have put there:
"select * from x where y=" + @.[user::max_date]
and i get an error.
can u advice?
|||Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.
-Jamie
|||
Jamie,
thank you very much for your help - you realy helped me here and saved me lots of time.
tomer
|||Hi Jamie,
yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?
why can u advice me on that please?
tomer
|||You have made a change in one of your components that means it won't work anymore. It should tell you which component.
-Jamie
|||
it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:
"select * from x where y = " + [user::x]
when x is a string type. i must have changed somthing small without noticing...?
|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows:
Anrijs Vitolins
Yeah you can do all of this. This link will get you started regarding the lookup and putting new records into the lookup table - I recommend method 2: http://www.sqlis.com/default.aspx?311
You'll need a MULTICAST transform in there at some point as well by the sounds of it.
-Jamie
Greetings,
I am using SQL Server 2005 and I want a clue if there is some good built-in support to do the following.
I want to transfer all the data from one source database to another destination database having same number to tables and schema like source database. I also want to ensure that the old data on the destination database should be secure too.
I have an idea that this can be done through SSIS in coding but can anyone give me a guide, tip, link, sample code that can be helpful.
Thanx a million in advance.
Regards
Try the link below for code samples from DTS/SSIS expert. Hope this helps.
http://www.sqlis.com/
This is my first project with SSIS and I'm having some problems migrating our current data import to it. Generally: right now we're pulling hourly data (which has a timestamp on it) in from a customer's db(which can't be modified, and is big and growing quickly) into our db. To do this we have been using a field to keep track of the last data download time (in our db) and then using that then when querying the customer's db to reduce the number of rows returned.
Will a similar system work in SSIS? I'm having trouble pulling the last download date out of the "target" database and using it in the query on the customer's db. Any advice on what I should be looking into to do this and am I headed in the right direction or just not thinking about this in a SSIS way?
How powerful/common are variables in SSIS and should I be trying to use them here?
Thanks,
Jeff
Variables are the way to go with this. Use an ExecuteSQL task to grab the timestamp and store it in a variable. Then either use the variable as a parameter on your data source SELECT statements in the data flow, or build the SQL dynamically, using expression based variables. Generally, if your data source is not SQL Server, it seems like you are better off using the expression based variables.
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
Performance you can achive using SSIS will be much better than that with Biztalk.|||
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.
-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?
Thanks,
JGP|||Are you using biztalk 2002 or 2004
Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.
My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.
There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.
Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.
Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?
The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.
Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.
I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.
It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
Performance you can achive using SSIS will be much better than that with Biztalk.|||
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.
-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?
Thanks,
JGP|||Are you using biztalk 2002 or 2004
Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.
My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.
There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.
Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.
Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?
The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.
Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.
I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.
It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
Performance you can achive using SSIS will be much better than that with Biztalk.|||
Hi,
We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.
Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?
I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?
Looking forward to any insights.
Thanks,
JGP
This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.
-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?
Thanks,
JGP|||Are you using biztalk 2002 or 2004
Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.
My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.
There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.
Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.
Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?
The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.
Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.
I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.
It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.
